Excel - lijst uitbreiden mbv een soort LEFT JOIN

[Tsjipmanz]

Ik heb in Excel een lijst met documenten en de projecten waar ze bij horen (er zijn meer kolommen maar even gesimplificeerd om tot de kern van het probleem te komen).

DocA P01
DocB P01
DocC P02

Ook heb ik een lijst met projecten en bijbehorende dossiermappen:

P01 Map1
P01 Map2
P02 Map4
P02 Map5

Voor een migratie wil ik alle documenten naar de bijbehorende dossiermappen kopiëren op basis van het project. De output zou dus moeten zijn:

DocA P01 Map1
DocA P01 Map2
DocA P01 Map3
DocB P01 Map1
DocB P01 Map2
DocB P01 Map3
DocC P02 Map4
DocC P02 Map5

Handmatig doen of zelfs slim copypasten is gezien de grote hoeveelheid gegevens (3500 documenten verdeeld over 213 projecten die 1477 dossiers bestrijken) geen optie.

Het lijkt op het eerste gezicht niet zo ingewikkelde operatie, maar het lukt me vooralsnog niet om voor elkaar te krijgen. Van mijn inmiddels flink roestige databasekennis zou je zeggen dat het een left join is van de eerste met de tweede tabel, met als join-conditie dat het project uit beide tabellen gelijk is. Maar goed, dit is Excel, geen database.

Iemand suggesties hoe dit aan te pakken?

[ShitHappens]

Hele grote kans dat dit wel gaat lukken met de VLOOKUP formule (in de Nederlandse versie van Excel heet deze V.ZOEKEN).
Daarmee kun je een waarde van een cel opzoeken binnen een ander bereik, en dan de x-de kolom binnen dat bereik retourneren. Zo kun je dan per document het P-nummer opzoeken in de lijst met mappen, en dan het mapnummer terugkrijgen.
Er zijn online diverse voorbeelden, how-to's en achtergrondinfo over te vinden.

[Tsjipmanz]

ShitHappens schreef op maandag 28 oktober 2019 @ 13:54:
Hele grote kans dat dit wel gaat lukken met de VLOOKUP formule (in de Nederlandse versie van Excel heet deze V.ZOEKEN).
Daarmee kun je een waarde van een cel opzoeken binnen een ander bereik, en dan de x-de kolom binnen dat bereik retourneren. Zo kun je dan per document het P-nummer opzoeken in de lijst met mappen, en dan het mapnummer terugkrijgen.
Er zijn online diverse voorbeelden, how-to's en achtergrondinfo over te vinden.

Bedankt. Jouw aanpak zou prima lukken als er per project maar één map zou zijn. Dit is echter niet het geval. Er zijn projecten met meer dan 100 bijbehorende dossiermappen. In dit geval zou een rij uit de documententabel voor elk van die dossiernummers gedupliceerd moeten worden, en volgens mij kan dat niet middels een VLOOKUP. Of begrijp ik je verkeerd?

[Djordjo]

Als je het in Excel wil doen, zonder VBA, zou ik een tabel maken met documenten vs. mappen, en een kruisje zetten indien de map hoort bij het project dat hoort bij het document.
Of, in plaats van het kruisje, direct het kopieercommando. Daarna is het slechts een kwestie van de niet-lege tabelwaarden in een kolom krijgen.

[ShitHappens]

Tsjipmanz schreef op maandag 28 oktober 2019 @ 14:00:
[...]


Bedankt. Jouw aanpak zou prima lukken als er per project maar één map zou zijn. Dit is echter niet het geval. Er zijn projecten met meer dan 100 bijbehorende dossiermappen. In dit geval zou een rij uit de documententabel voor elk van die dossiernummers gedupliceerd moeten worden, en volgens mij kan dat niet middels een VLOOKUP. Of begrijp ik je verkeerd?

Oh nee inderdaad, ik meende even te zien dat er een 1:1:1 relatie was tussen document, project en map Dan werkt inderdaad VLOOKUP niet zomaar.

Je zou dan misschien beter kunnen kijken naar datamanipulatie met de SQL Query tool in Excel, of zelfs de Power Query plugin. Daarmee kun je een nieuwe tabel aanmaken, op basis van bestaande data, gedefinieerd in SQL ipv formules.

[dix-neuf]

@Tsjipmanz,
Hoe kun je DocA en DocB aan map3 in P01 toewijzen als map3 in je gegevens niet blijkt te bestaan ?

[F_J_K]

Dat zal een foutje in het voorbeeld zijn, gok ik.

een soort LEFT JOIN

Je kunt een echte left join doen

Je kunt een Query doen en dan de xls file als bron opgeven, en dan letterlijk de left join uitvoeren.

Edit: https://superuser.com/que...n-excel-as-i-would-in-sql

[ Voor 19% gewijzigd door F_J_K op 28-10-2019 15:53 ]

[Tsjipmanz]

dix-neuf schreef op maandag 28 oktober 2019 @ 15:40:
@Tsjipmanz,
Hoe kun je DocA en DocB aan map3 in P01 toewijzen als map3 in je gegevens niet blijkt te bestaan ?

Bij het maken van m'n voorbeeld heb ik een fout gemaakt. Scherp.

Het is nu voor elkaar, maar hiervoor heb ik de gegevens verstrekt aan een behulpzame BI-collega die de tabellen in Business Objects heeft ingelezen en het in die omgeving heeft gefixt. Bedankt voor de hulp, ik vrees ook dat er geen eenvoudige manier is om dit in Excel te doen.

[ShitHappens]

Tsjipmanz schreef op maandag 28 oktober 2019 @ 15:55:
[...]
ik vrees ook dat er geen eenvoudige manier is om dit in Excel te doen.

Er zijn 2 reacties geweest om het relatief makkelijk in Excel op te lossen met Query tools Bijna dezelfde aanpak maar dan zonder BI-tussenkomst.
Alsnog mooi dat 't opgelost is!

[dix-neuf]

Het kan ook met een macro.



Als de gegevens in het eerste blad van een Excelmap staan zoals afgebeeld hierboven, dan kun je onderstaande macro gebruiken. Het resultaat verschijnt dan in de kolommen G, H, en I .

code:

Sub macro1()
Dim a As Integer, p As Integer, x As Integer, y As Integer
With Sheets(1)
.Columns("g:i").ClearContents
x = 2: y = 2
Do While x <= .Cells(.Rows.Count, 1).End(xlUp).Row
a = Application.CountIf(.Range("d2:d9"), .Cells(x, 2))
.Range(.Cells(x, 1), .Cells(x, 2)).Copy .Range(.Cells(y, 7), .Cells(y + a - 1, 7))
For p = 2 To .Cells(.Rows.Count, 4).End(xlUp).Row
If .Cells(p, 4) = .Cells(y, 8) Then
.Cells(p, 5).Copy .Cells(y, 9)
y = y + 1
End If
Next p
x = x + 1
Loop
End With
End Sub

[Tsjipmanz]

[quote]ShitHappens schreef op maandag 28 oktober 2019 @ 17:19:
[...]

Er zijn 2 reacties geweest om het relatief makkelijk in Excel op te lossen met Query tools Bijna dezelfde aanpak maar dan zonder BI-tussenkomst.
|/quote]

Je hebt gelijk, maar een welwillende collega om hulp vragen was voor nu even de meest pragmatische en snelle oplossing. Als ik een volgende keer iets meer tijd heb om te "hobbyen" zal ik zeker naar de (power-)query oplossingen kijken, ik was me niet bewust van deze mogelijkheden in Excel.