[Excel] 2 CSV bestanden vergelijken op Artikelnummer

[donnie1992]

Hallo,

Ik heb hier 2 excel/CSV bestanden, Bestand1.csv en Bestand2.csv.
Bestand 1 heeft ongeveer 70K entries en bestand 2 ongeveer de helft. Beide bestanden zit een kolom met SKU/Productnummers die overeenkomen. Nu moet ik van bestand 1 de entries eruit halen die niet in bestand 2 zitten, Of een nieuw bestand waar alleen de gematchde nummers instaan met de gegevens van bestand 1 .
Als je het nog volgt, perfect! Ik heb al het een en ander geprobeerd met VLOOKUP en AWX maar ik kom hier niet uit.
Iemand die mij verder kan helpen ?

[heuveltje]

..

[Voor 100% gewijzigd door heuveltje op 19-10-2018 13:34. Reden: oops]

[heuveltje]

VLOOKUP zou hierin toch echt je vriend meoten zijn ?
zoek hetzelfde nr artikelnr op, en filter vervolgens waar deze een error geeft (en dus niet voorkomt)

Geen idee wat awx is/doet ?

[donnie1992]

heuveltje schreef op vrijdag 19 oktober 2018 @ 13:34:
VLOOKUP zou hierin toch echt je vriend meoten zijn ?
zoek hetzelfde nr artikelnr op, en filter vervolgens waar deze een error geeft (en dus niet voorkomt)

Geen idee wat awx is/doet ?

https://www.gnu.org/software/gawk/
Scheen ook een methode te zijn om dit soort dingen uit te voeren.
Ik heb dus nu de 2 lijsten elk in een aparte sheet/werkblad staan en een 3e werkblad open waar dus de gegevens in zouden moeten komen. Als ik dan =VLOOKUP(Blad2!A:A,Blad3!A:A,2) dan krijg ik een foutmelding. Nu moet ik zeggen dat ik totaal niet bekend ben met VLookup, kan je mij op weg helpen?

[Orion84]

donnie1992 schreef op vrijdag 19 oktober 2018 @ 14:20:
[...]

Als ik dan =VLOOKUP(Blad2!A:A,Blad3!A:A,2) dan krijg ik een foutmelding. Nu moet ik zeggen dat ik totaal niet bekend ben met VLookup, kan je mij op weg helpen?

De help functie van Excel, waarin heel duidelijk staat uitgelegd hoe die formule werkt

Daar leer je een stuk meer van dan dat iemand het hier voor je voorkauwt.
Hint 1: dat eerste argument wat je meegeeft klopt niet, dat moet een enkele cel zijn
Hint 2: vergeet het optionele laatste argument niet, dat is in dit geval heel belangrijk
Hint 3: Je kan niet een zoekrange van 1 kolom opgeven, maar dan wel een 2 invullen als 3e parameter

[Voor 32% gewijzigd door Orion84 op 19-10-2018 14:31]

[Icephase]

donnie1992 schreef op vrijdag 19 oktober 2018 @ 14:20:
[...]

https://www.gnu.org/software/gawk/
Scheen ook een methode te zijn om dit soort dingen uit te voeren.
Ik heb dus nu de 2 lijsten elk in een aparte sheet/werkblad staan en een 3e werkblad open waar dus de gegevens in zouden moeten komen. Als ik dan =VLOOKUP(Blad2!A:A,Blad3!A:A,2) dan krijg ik een foutmelding. Nu moet ik zeggen dat ik totaal niet bekend ben met VLookup, kan je mij op weg helpen?

Je gebruikt VLOOKUP verkeerd. Het eerste argument verwijst naar de cel die je wil vergelijken (dus niet een range). Het tweede argument zijn de kolommen waarin je wil zoeken, waarbij de waarde in de eerste kolom moet voorkomen én waarvan de indeling hetzelfde moet zijn (tekst of getal of standaard etc). Het derde argument is het indexgetal van de kolom uit de range die je erbij wilt zoeken. Dus range A:B met als derde argument 2 geeft de waarde uit kolom B.
Het vierde argument is optioneel (false of true), dat is of Excel echt de exacte waarde moeten zoeken (false) of dat hij zelf een afweging maakt of het overeenkomt (true). Standaard staat deze op false, en dat wil je meestal ook.

[Orion84]

Icephase schreef op vrijdag 19 oktober 2018 @ 14:26:
[...]
Het vierde argument is optioneel (false of true), dat is of Excel echt de exacte waarde moeten zoeken (false) of dat hij zelf een afweging maakt of het overeenkomt (true). Standaard staat deze op false, en dat wil je meestal ook.

Was dat maar waar, die staat default (als je hem weglaat dus) op true.

[Icephase]

Orion84 schreef op vrijdag 19 oktober 2018 @ 14:32:
[...]
Was dat maar waar, die staat default (als je hem weglaat dus) op true.

Verrek, je hebt gelijk... nooit geweten, maar gelukkig laat ik hem nooit weg. @TS: dus ook ff als 4e argument FALSE invullen!

[donnie1992]

Icephase schreef op vrijdag 19 oktober 2018 @ 14:26:
[...]


Je gebruikt VLOOKUP verkeerd. Het eerste argument verwijst naar de cel die je wil vergelijken (dus niet een range). Het tweede argument zijn de kolommen waarin je wil zoeken, waarbij de waarde in de eerste kolom moet voorkomen én waarvan de indeling hetzelfde moet zijn (tekst of getal of standaard etc). Het derde argument is het indexgetal van de kolom uit de range die je erbij wilt zoeken. Dus range A:B met als derde argument 2 geeft de waarde uit kolom B.
Het vierde argument is optioneel (false of true), dat is of Excel echt de exacte waarde moeten zoeken (false) of dat hij zelf een afweging maakt of het overeenkomt (true). Standaard staat deze op false, en dat wil je meestal ook.

OH, Ik snap wat je bedoelt, hij vult dat lijstje natuurlijk per cel aan.
Dus als ik de range kolommen B t/m F wil toevoegen dan is dat dus 2:6 van Blad 1 ?
Want ik krijg nu de hele tijd deze foutmelding, ook vind ik vlookup niet terug als ik via de FX knop een formule aan wil maken, Moet ik eerst ergens een optie aanzetten ofzo? Dit is mijn formule:

code:

=VLOOKUP(Blad3!A2,Blad2!A2,2,FALSE)

Kan dat te maken hebben omdat ik dus ook Komma-seperated CSV bestanden heb waar die de data uit haalt ?

[Voor 6% gewijzigd door donnie1992 op 19-10-2018 15:53]

[Orion84]

Nederlandse excel zo te zien? Dan heten al die formules anders

Verticaal_zoeken of iets dergelijks.

[Icephase]

Denk het niet; je 2e argument is nog steeds geen range. En de juiste formule is VERT.ZOEKEN()

[Voor 26% gewijzigd door Icephase op 19-10-2018 16:01]

[donnie1992]

Icephase schreef op vrijdag 19 oktober 2018 @ 16:01:
Denk het niet; je 2e argument is nog steeds geen range. En de juiste formule is VERT.ZOEKEN()

OH, Excuses
En inderdaad, de functie heet inderdaad Vert.Zoeken, nu kan ik idd ook normale formule "wizard" gebruiken.
Nu klopt mijn formule geloof ik wel, nu krijg ik alleen als waarde #N/B in de cellen. De celeigenschappen van beide kolommen in de gegevensbladen staan als getal, Wat doe ik nu nog fout ?

code:

=VERT.ZOEKEN(Blad2!A3;Blad3!A:A;Blad3!B:CW;ONWAAR)

Ik denk zelf het 3e argument, maar als ik deze verander naar een andere/enkele waarde dan krijg ik dezelfde fout. We zijn zo dichtbij

[B-Real]

Eigenlijk gewoon even de Microsoft uitleg doorlezen, zie: https://support.office.co...fe-4963-8ab8-93a18ad188a1

Hierbij toch een poging om het kort toe te lichten, gebruik allereerst gewoon de formule wizard dat maakt het al makkelijker:

Stap 1: selecteer de cel die je wilt vergelijken met het andere bestand. Dus bijv. cel A1 uit bestand 1
Stap 2: selecteer het bereik waarin Excel de waarde uit cel A1 uit bestand 1 moet opzoeken. Dus bijv. de kolommen B t/m F uit bestand 2
Stap 3: geef aan uit welke van de kolommen B t/m F Excel de waarde moet retourneren. Dus als de artikelnummers in bestand 2 in kolom C staan dan typ je een 2. Kolom C is immers de tweede geselecteerde kolom in B t/m F. (je zou dus ook alleen kolom C kunnen selecteren ipv B t/m F, dan is het dus kolom 1).
Stap 4: geef aan of Excel moet zoeken op een exacte waarde of niet. Ik vul hier vrijwel altijd 0 of onwaar in, beide hebben hetzelfde resultaat. Excel zal alleen een exacte waarde meenemen.

Paar opmerkingen:
- Als Excel de waarde uit cel A1 niet kan vinden dan zal het resultaat #N/B zijn. Kun je met de formule ALS.NB ook nog aanpassen, maar is een stap verder.
- De vert. zoeken formule retourneert altijd de 1e hit, komt het artikelnummer nog 3x voor dan pakt hij altijd de eerste. Kan verschil maken als je bijv. de aantallen verschillen in kolom F bijv. wil opzoeken.

[donnie1992]

Misschien word het duidelijker als ik uitleg wat de bedoeling is.
Ik heb dus een lijst met producten en eigenschappen van 1 bron, en een lijst met producten van een andere.
De enige overeenkomst tussen die 2 lijsten zijn de SKU nummers, Nu wil ik dus de attributen van de 2e lijst overgekopieerd hebben naar de andere maar dus alleen van de SKU's die in de 1e lijst staan.

@B-Real, geloof me, ik heb toch echt gegoogled, maar soms heb je even een fris paar ogen nodig, ben hier af en aan al een paar dagen mee bezig om een oplossing te zoeken, vandaar

[Voor 19% gewijzigd door donnie1992 op 19-10-2018 16:18]

[Eppo ©]

CSV1 inlezen in Excel op blad 1
CSV2 inlezen in Excel op blad 2

Op blad twee gebruik je de volgende formule:
=VERGELIJKEN([SKU nummer op blad 2];[De kolom met SKU nummers op blad 1];0)

Daarna kopieer je alle rijen waar de formule een nummer geeft naar een nieuw blad en die exporteer je weer naar een CSV (of naar wat je ook nodig hebt). Overal waar je #N/B krijgt zijn SKU's die niet op blad 1 stonden.

[pacificocean]

Volgens mij gebruik je de verkeerde applicatie. In MS Acces is dit zo geregeld.

[donnie1992]

Eppo © schreef op vrijdag 19 oktober 2018 @ 16:20:
CSV1 inlezen in Excel op blad 1
CSV2 inlezen in Excel op blad 2

Op blad twee gebruik je de volgende formule:
=VERGELIJKEN(~~~[SKU nummer op blad 2];~~~[De kolom met SKU nummers op blad 1];0)

Daarna kopieer je alle rijen waar de formule een nummer geeft naar een nieuw blad en die exporteer je weer naar een CSV (of naar wat je ook nodig hebt). Overal waar je #N/B krijgt zijn SKU's die niet op blad 1 stonden.

OH, Ik doe dit op een nieuw 3e werkblad, is dat waar de fout zit ?
Over het #n/b verhaal, In principe moet elke waarde van de kolom A2 van het 1e argument terug te vinden zijn in het 2e bestand, het 1e bestand is hier immers van afgeleidt.

[Voor 12% gewijzigd door donnie1992 op 19-10-2018 16:26]

[Orion84]

pacificocean schreef op vrijdag 19 oktober 2018 @ 16:22:
Volgens mij gebruik je de verkeerde applicatie. In MS Acces is dit zo geregeld.

In excel ook, als je snapt hoe vlookup / vert.zoeken werkt. Dus laten we het vooral niet nog ingewikkelder maken door er weer een ander programma bij te halen.

[donnie1992]

pacificocean schreef op vrijdag 19 oktober 2018 @ 16:22:
Volgens mij gebruik je de verkeerde applicatie. In MS Acces is dit zo geregeld.

Orion84 schreef op vrijdag 19 oktober 2018 @ 16:26:
[...]

In excel ook, als je snapt hoe vlookup / vert.zoeken werkt. Dus laten we het vooral niet nog ingewikkelder maken door er weer een ander programma bij te halen.

In SQL is dit idd een stuk makkelijker, dus misschien niet eens zo'n slecht idee
Het CSV moet namelijk ook weer geimporteerd worden in een database.
De reden dat ik niet gewoon direct de 2e csv importeer is omdat er een berekening over het nieuwe csv bestand moet worden gemaakt voordat dit gebeurd.

[Voor 16% gewijzigd door donnie1992 op 19-10-2018 16:28]

[heuveltje]

donnie1992 schreef op vrijdag 19 oktober 2018 @ 16:27:
[...]


[...]


In SQL is dit idd een stuk makkelijker, dus misschien niet eens zo'n slecht idee
Het CSV moet namelijk ook weer geimporteerd worden in een database.
De reden dat ik niet gewoon direct de 2e csv importeer is omdat er een berekening over het nieuwe csv bestand moet worden gemaakt voordat dit gebeurd.

Als het toch in sql moet, en je weet hoe dat werkt is het idd ook prima daar in te doen.
Maar in excel stelt dit ook echt geen zak voor

donnie1992 schreef op vrijdag 19 oktober 2018 @ 16:24:
[...]

OH, Ik doe dit op een nieuw 3e werkblad, is dat waar de fout zit ?
Over het #n/b verhaal, In principe moet elke waarde van de kolom A2 van het 1e argument terug te vinden zijn in het 2e bestand, het 1e bestand is hier immers van afgeleidt.

Je kunt het op een 3e werkblad doen, dat maakt alleen de formule nog wat langer.
=VLOOKUP(Blad2!A1;Blad3!A:A;1;onwaar)

is dan grofweg wat je zoekt denk ik.

[Voor 35% gewijzigd door heuveltje op 19-10-2018 16:31]

[B-Real]

donnie1992 schreef op vrijdag 19 oktober 2018 @ 16:17:
Misschien word het duidelijker als ik uitleg wat de bedoeling is.
Ik heb dus een lijst met producten en eigenschappen van 1 bron, en een lijst met producten van een andere.
De enige overeenkomst tussen die 2 lijsten zijn de SKU nummers, Nu wil ik dus de attributen van de 2e lijst overgekopieerd hebben naar de andere maar dus alleen van de SKU's die in de 1e lijst staan.

@B-Real, geloof me, ik heb toch echt gegoogled, maar soms heb je even een fris paar ogen nodig, ben hier af en aan al een paar dagen mee bezig om een oplossing te zoeken, vandaar

Naja, aan je formule te zien snap je de werking van de functie vert.zoeken nog niet helemaal dus ik zou het nog maar een keer gaan lezen dan

donnie1992 schreef op vrijdag 19 oktober 2018 @ 16:24:
[...]

OH, Ik doe dit op een nieuw 3e werkblad, is dat waar de fout zit ?
Over het #n/b verhaal, In principe moet elke waarde van de kolom A2 van het 1e argument terug te vinden zijn in het 2e bestand, het 1e bestand is hier immers van afgeleidt.

Maakt niet heel veel uit, als je dat wilt:
- Plak in het 3e werkblad alle artikelnummers in kolom A.
- Zoek de waarden uit kolom A op middels vert.zoeken in bestand/werkblad 2

[donnie1992]

B-Real schreef op vrijdag 19 oktober 2018 @ 16:29:
[...]


Naja, aan je formule te zien snap je de werking van de functie vert.zoeken nog niet helemaal dus ik zou het nog maar een keer gaan lezen dan


[...]


Maakt niet heel veel uit, als je dat wilt:
- Plak in het 3e werkblad alle artikelnummers in kolom A.
- Zoek de waarden uit kolom A op middels vert.zoeken in bestand/werkblad 2

(Dat heb je wel eens, dat iets ogenschijnlijk simpels een onmogelijke taak lijkt te zijn omdat je over dingen heen begint te kijken. )

Dat is idd ook een goeie, dat was een optie die ik in mijn hoofd had, maar omdat ik al niet uit het vlookup gedeelte kwam nog niet van gekomen, Tot Dusver erg bedankt! Ik ga zelf even aan het knoeien en als ik er echt niet uit kom trek ik even aan de bel, ik ben iig verder dan 3 dagen geleden

[Voor 8% gewijzigd door donnie1992 op 19-10-2018 16:34]

[Icephase]

donnie1992 schreef op vrijdag 19 oktober 2018 @ 16:13:
[...]

OH, Excuses
En inderdaad, de functie heet inderdaad Vert.Zoeken, nu kan ik idd ook normale formule "wizard" gebruiken.
Nu klopt mijn formule geloof ik wel, nu krijg ik alleen als waarde #N/B in de cellen. De celeigenschappen van beide kolommen in de gegevensbladen staan als getal, Wat doe ik nu nog fout ?

code:

=VERT.ZOEKEN(Blad2!A3;Blad3!A:A;Blad3!B:CW;ONWAAR)

Ik denk zelf het 3e argument, maar als ik deze verander naar een andere/enkele waarde dan krijg ik dezelfde fout. We zijn zo dichtbij

Je 3e argument is hier niet goed inderdaad. Dat moet een "kolomindex-getal" zijn, dus 'de hoeveelste kolom van de range in je 2e argument' moet Excel gaan weergeven vanuit de formule.

Een normale VERT.ZOEKEN functie ziet er zo uit:

code:

=VERT.ZOEKEN(Blad1!A2;Blad2!A:B;2;onwaar)

Excel gaat dan naar de waarde uit "Blad1, cel A2" zoeken in eerste kolom van de range "Blad 2, kolommen A en B". Vind hij exact dezelfde waarde, geeft hij de bijbehorende waarde uit de 2e kolom van de range weer (is dus je 3e argument). Vind hij meerdere exact gelijke waardes, zal hij alléén de eerste weergeven. Verticaal zoeken heeft dus z'n beperkingen, maar voor jouw toepassing hoeft dat geen probleem te zijn.

[donnie1992]

Ik ben er achter waar ik de mist in ging.
Bij het selecteren van de lookup range had ik inderdaad alleen de desbetreffende prijskolom geselecteerd waardoor die dus ook maar 1 kolom index number beschikbaar is, Nadat ik de rank had uitgebreid naar meerdere kolommen werkt alles perfect. Zoals ik al dacht, was het een kleine fout die ik waarschijnlijk nooit meer zal maken