[Excel] sheets samenvoegen om ERP te voeden

[Chefferson]

Mijn vraag

Tweakers, jullie hebben mij al vaker uit de brand geholpen (en ik hopelijk ook andere tweakers) en daarom een volgende uitdaging. Ik heb 2 twee excel bestanden die samen moeten gaan werken en tot één output bestand moeten komen. De ene is een besteld bestand (met artikelen) en de ander een bestand wel shipping kosten berekend. Even voor de goede orde, ik ben geen shady dropshipper.

Bestand 1:

Klant Order Line Aantal Prijs
Test 1 123 a 1 € 2,00
b 2 € 2,00
c 3 € 2,00
d 4 € 2,00
Test 2 234 a 1 € 2,00


Bestand 2:

Order Name Quantity Price
123 Shipping 1 € 5,00
234 Shipping 1 € 5,00

Nu moet ik een manier vinden om automatisch de shipping regel onder de order te plaatsen op basis van het order nummer.
Dus excel moet een regel vrij maken onder de order en daar deze shipping regel aan toevoegen.
Met uiteindelijk resultaat:

Klant Order Line Aantal Prijs
Test 1 123 a 1 € 2,00
b 2 € 2,00
c 3 € 2,00
d 4 € 2,00
123 Shipping 1 € 5,00
Test 2 234 a 1 € 2,00
234 Shipping 1 € 2,00


Relevante software en hardware die ik gebruik
Excel 365 (Engelse versie)

Wat ik al gevonden of geprobeerd heb


Ik heb echt geen idee waarop ik moet zoeken om dit voor elkaar te krijgen.
Meerdere zoekopdrachten, etc. Believe me, i've tried.

Hopelijk kunnen jullie mij helpen!
...

[HUW]

Misschien dat ik iets mis, maar dit kan toch gewoon met VLOOKUP?

[Sethro]

Begin is met het in logische stappen opschrijven wat je probeert te bereiken:
1) data kopiëren naar dezelfde tabel
2) logische sorteersleutel maken
3) … etc

[Chefferson]

HUW schreef op dinsdag 7 januari 2025 @ 21:21:
Misschien dat ik iets mis, maar dit kan toch gewoon met VLOOKUP?

Helaas niet. Want excel moet een extra regel onder de order plaatsen met de shipping cost in een bestaande tabel. Dus --> invoegen lege regel --> data uit andere sheet halen op basis van order nummer --> deze kopieren in de nieuwe regel (zonder andere regels te overschrijven)

Het uiteindelijke doel is om deze in te lezen (orders + shipping) om een totaal overzicht te krijgen.

[ Voor 10% gewijzigd door Chefferson op 07-01-2025 21:27 ]

[Chefferson]

Sethro schreef op dinsdag 7 januari 2025 @ 21:21:
Begin is met het in logische stappen opschrijven wat je probeert te bereiken:
1) data kopiëren naar dezelfde tabel
2) logische sorteersleutel maken
3) … etc

De data van tabel B (shipping costs) moet worden toegevoegd aan tabel a (orders). De enige unieke sleutel die die ik heb is het ordernummer.

[Sethro]

Chefferson schreef op dinsdag 7 januari 2025 @ 21:26:
[...]


De data van tabel B (shipping costs) moet worden toegevoegd aan tabel a (orders). De enige unieke sleutel die die ik heb is het ordernummer.

Je zult iets van logica moeten bedenken hoe dat goed te sorteren. Want lijkt nu wat willekeurig.

Het kopiëren en daarna sorteren is koud kunstje, met een paar kliks of simpele macro.

[ Voor 36% gewijzigd door Sethro op 07-01-2025 21:31 ]

[oohh]

Misschien niet de meest professionele oplossing, dat zou waarschijnlijk met Visual Basic code zijn. Maar daar heb ik geen ervaring mee.

Kun je niet de tekst met een VLOOKUP of XLOOKUP onder de huidige tabel plaatsen en daarna de data op ordernummer sorteren zodat de data op de juiste volgorde staat?

[ Voor 7% gewijzigd door oohh op 07-01-2025 21:30 ]

[Chefferson]

Sethro schreef op dinsdag 7 januari 2025 @ 21:27:
[...]


Je zult iets van logica moeten bedenken hoe dat goed te sorteren. Want lijkt nu wat willekeurig.

De logica is er.
a) order lijst met orders (soms 1, soms 4, soms 100)
b) lijst met berekende transport kosten (obv aantallen, gewicht, etc)

Het lastige zit hem hierin:

Extra regel automatisch laten toevoegen door excel onder de order.

Kan excel uberhaupt onder een set regels (1 order bestaande uit meerdere producten) uit zichzelf
een regel leegmaken (overige orders verplaatsen naar beneden) en een regel uit een ander bestand (zelfde orderregel (logica is orderregel tegen shipping cost) er aan toevoegen?

[The Eagle]

Als je de boel toch al in moet lezen zou ik de koppeling na het inlezen leggen. Eenmaal in een database is zoiets een redelijke peulenschil geworden.

Excel is geen database, en ook geen tool voor transformatie van business data. Al denkt business meestal van wel.

Als Excel je enige systeem is: ga op zoek naar iets wat beter bestand is tegen dingen als bestands corruptie, een backup en restore heeft en meerdere users tegelijkertijd aan kan zonder elkaar in de weg te zitten. Of meerdere versies van de waarheid kan je nen

[Chefferson]

oohh schreef op dinsdag 7 januari 2025 @ 21:29:
Misschien niet de meest professionele oplossing, dat zou waarschijnlijk met Visual Basic code zijn. Maar daar heb ik geen ervaring mee.

Kun je niet de tekst met een VLOOKUP of XLOOKUP onder de huidige tabel plaatsen en daarna de data op ordernummer sorteren zodat de data op de juiste volgorde staat?

Ik kan het bestand enkel inlezen als de shipping regel direct onder de order regel staat. Dus per order. En niet totaal ergens onderaan helaas.

[The Eagle]

Chefferson schreef op dinsdag 7 januari 2025 @ 21:32:
[...]


De logica is er.
a) order lijst met orders (soms 1, soms 4, soms 100)
b) lijst met berekende transport kosten (obv aantallen, gewicht, etc)

Het lastige zit hem hierin:

Extra regel automatisch laten toevoegen door excel onder de order.

Kan excel uberhaupt onder een set regels (1 order bestaande uit meerdere producten) uit zichzelf
een regel leegmaken (overige orders verplaatsen naar beneden) en een regel uit een ander bestand (zelfde orderregel (logica is orderregel tegen shipping cost) er aan toevoegen?

Nee. Zal via macros wellicht kunnen maar dan ben je al een soort van applicatie aan het bouwen.

Het is in de basis een tool om mee te rekenen. Je kunt er inmiddels veel meer mee, maar het is nog steeds geen backend

[The Eagle]

Chefferson schreef op dinsdag 7 januari 2025 @ 21:33:
[...]


Ik kan het bestand enkel inlezen als de shipping regel direct onder de order regel staat. Dus per order. En niet totaal ergens onderaan helaas.

Dom inlees systeem dan. Of een luie applicatie beheerder daarvan

[Chefferson]

The Eagle schreef op dinsdag 7 januari 2025 @ 21:32:
Als je de boel toch al in moet lezen zou ik de koppeling na het inlezen leggen. Eenmaal in een database is zoiets een redelijke peulenschil geworden.

Excel is geen database, en ook geen tool voor transformatie van business data. Al denkt business meestal van wel.

Als Excel je enige systeem is: ga op zoek naar iets wat beter bestand is tegen dingen als bestands corruptie, een backup en restore heeft en meerdere users tegelijkertijd aan kan zonder elkaar in de weg te zitten. Of meerdere versies van de waarheid kan je nen

Dit is dus juist waar ik tegenaan loop. Deze data moet worden ingelezen in een ERP pakket. En die accepteert dit enkel zo. De bron data komt uit twee systemen. Een order systeem, en de kosten berekent per verzending in een apart bestand.

[brokenp]

Als je het opdeelt in meerdere stappen en wat handmaitig copy/paste accepteert zou ik het volgende doen:
1 iets “slims” doet met nummering (elke ordernummer een nummer geven om later opnte sorteren, $ordernummer_$regelnummer oid),
2 daarna in het andere tabblad met vlookup een unieke regelnummer maakt dat altijd op het eind van de order komt ($ordernummer_999)
3 de data uit tabblad 1 en 2 handmatig kopiëren naar nieuw tabblad
4 dat nieuwe tabblad sorteren op “regelnummer” en daarna die tijdelijke kolom weggooien

[ Voor 9% gewijzigd door brokenp op 07-01-2025 21:51 ]

[Chefferson]

brokenp schreef op dinsdag 7 januari 2025 @ 21:49:
Als je het opdeelt in meerdere stappen en wat handmaitig copy/paste accepteert zou ik het volgende doen:
1 iets “slims” doet met nummering (elke ordernummer een nummer geven om later opnte sorteren, $ordernummer_$regelnummer oid),
2 daarna in het andere tabblad met vlookup een unieke regelnummer maakt dat altijd op het eind van de order komt ($ordernummer_999)
3 de data uit tabblad 1 en 2 handmatig kopiëren naar nieuw tabblad
4 dat nieuwe tabblad sorteren op “regelnummer” en daarna die tijdelijke kolom weggooien

Bedankt voor je input. Helaas gaat het om 1000-en records. Dus handmatig is niet het code woord. Vandaar deze outreach op tweakers

[The Eagle]

Je probleem zit hem er in dat je relevante info voor de order niet repliceert in je line items, ongeacht of dat nou een fysiek item of een shipping item is

Voor het inlezen zal dat naar verwachting niks uitmaken, maar check dat met je erp beheerder. Vraag hem maar eens of je op 1 regel ordernummer, item volgnummer en de rest mag aanleveren. Denk stiekem dat ie dat alleen maar handig vindt

Om hoeveel orders per dag / maand gaat het? En welk ERP systeem?

* The Eagle slechts 13 jaar ervaring met financiële ERP systemen, there's always a way

[ Voor 11% gewijzigd door The Eagle op 07-01-2025 22:03 ]

[The Eagle]

Overigens zou ik verwachten dat voor het inlezen het niks uitmaakt in welke volgorde de line items staan. Ik verwacht zelfs dat de line nummers misschien niet eens nodig zijn, dat kan zo'n systeem prima zelf doornummeren.

[brokenp]

Chefferson schreef op dinsdag 7 januari 2025 @ 21:58:
[...]


Bedankt voor je input. Helaas gaat het om 1000-en records. Dus handmatig is niet het code woord. Vandaar deze outreach op tweakers

Denk dat je mijn oplossing niet snapt, is geen 1000 handmatieg acties, 3 ofzo..

1 voer extra kolom toe aan tabbald 1 genaaamd sort-nr
2 bedenk wat formules zodat je sort-nr voor de eerste regels uit je voorbeeld de colgedne inhoud heeft voor de eerste 5 regels: 123-1,123-2,123-3, 123-4, 234-1

3 Dan in tabblad 2 een kolommtoevoegen sottnr, Maak dat 123-99999 en 234-99999 oid

4 Kopieer 1 en 2 omder elkaar in een nieuw tabblad
5 sorteer op sortnr
Voila, nu staan regels 123-1,123-2,123-3,123-4 en 123-99999(transport) onder elkaar en daarna 234-1,234-99999

[Jarno]

De boel omzetten naar csv en dan met Python wat proberen is misschien ook nog een goede optie?

[tmagus]

Tip: kijk eens wat je met powerquery kunt doen. Eigenlijk heb je twee tabellen die je met een unieke key kunt verbinden.
Je kunt zo dan een powerquery schreven die uw data uit de twee tabellen combineert. Het resultaat gaat dan terug naar een sheet.

[The Eagle]

Misschien moet je eens nadenken of de oplossingsrichting die nu gekozen is (vooraf in Excel samenvoegen en dan inlezen) wel de juiste is.
Gewoon facturen inlezen en dan achteraf per factuur een regel toe voegen in het erp systeem, of in batch, kan ook een prima optie zijn.
Iemand moet die transport kosten bepalen. Als diegene ze in een Excel sheet kan opvoeren, kan ie dat ook in een Erp systeem. Of in de zelfde Excel sheet (niet aan te raden ).

[F_J_K]

Inhoudelijk: vraag je inderdaad erg af of 'klassieke' Excel wel het juiste tool is. Misschien ook beter om een uurtje of wat een expert in te huren. Bijvoorbeeld via degene die het ERP heef ingericht. Zo te horen is het nogal kostbaar als er denkfoutjes in zitten.

Excel uitdaging 2.0

offtopic:
Sowieso pas ik die topictitle aan. Er zijn meer mensen die ooit meer dan 1 vraag over Excel hebben gesteld. Het zou nogal een soepzooitje worden als iedereen met een vraag die titel zou nemen Probeer ajb voortaan een topictitel te bedenken die de kern van de daadwerkelijke vraag omschrijft.

[dixet]

In je voorbeeld lijkt het er op dat in Bestand 1 niet bij alle records het klant- en ordernummer staat? Als je gegevens gaat bewerken en combineren is dat wel een vereiste, de sleutelvelden missen nu in een deel van de data. Maar in Power Query (in Excel) is dat nog wel op te lossen.

Als het een gegeven is dat
a) de bronsystemen het niet in een gestructureerd formaat kunnen aanleveren (dus met klant- en ordernummer in iedere regel)
b) het ERP systeem echt maar 1 importformaat kent

zou ik het zo oplossen:

In Power Query:

1. Importeer beide bestanden (ik noem ze nu Bestand 1 en Bestand 2)
2. Gebruik in Bestand 1 de optie Fill Down om de ontbrekende klant- en ordernummers te vullen met die van het record erboven
3. Voeg een Index kolom toe aan beide bestanden om te zorgen dat je altijd dezelfde volgorde van record kan houden, ongeacht de latere bewerkingen
4. Voeg in beide bestanden een hulpkolom "bronbestand "toe om na samenvoeging te herkennen uit welk bestand het record komt. Hier ga je later op sorteren. In Bestand 1 vul je die hele kolom met de waarde "1", in Bestand 2[ vul je die hele kolom met de waarde "2"
5. Voeg beide tabellen samen met Merge. Power Query zal kolommen met dezelfde naam samenvoegen en met unieke namen als aparte kolommen in je nieuwe tabel opnemen
6. Sorteer de nieuwe tabel op 1) Ordernummer 2) bronbestand 3) Index
7. Maak een nieuwe tabel die alleen de gewenste kolommen overneemt voor de uitvoer (dus niet de Index etc) en eventueel leeg maakt (order- en klantnummer bij alle niet-eerste orderregels)

Niet fraai, maar het werkt wel

[Chefferson]

tmagus schreef op dinsdag 7 januari 2025 @ 22:27:
Tip: kijk eens wat je met powerquery kunt doen. Eigenlijk heb je twee tabellen die je met een unieke key kunt verbinden.
Je kunt zo dan een powerquery schreven die uw data uit de twee tabellen combineert. Het resultaat gaat dan terug naar een sheet.

Ik ga mij hier eens in verdiepen!

[sanger1981]

Is dit eenmalig of regelmatig?

Als dit eenmalig is zou je eventueel nog iets kunnen doen mettransponeren, kolom erachter plakken en die vullen met VLOOKUP en dan weer terug transponeren