Auteur Topic: combineren van verticaal zoeken (gelezen 50443 keer)

anjar · « **Gepost op:** 31 oktober 2019, 17:06:41 »

Bijgevoegd een bestandje met twee tabbladen. Er is geen unieke link. nu wil ik door 3 items te vergelijken de omschrijving op het tabblad nieuw vullen.
MAW als kostensoort, periode en bedrag van tabblad 1 en tabblad 2 gelijk zijn dat hij de omschrijving overneemt van tabblad 2, meer vergelijkingsmateriaal is er niet en alles staat doorelkaar. Dit is een voorbeeld bestand. Het echte bestand bevat ongeveer 270 000 regels. wat handmatig erg veel werk is en foutgevoeliger.

Hopelijk kan iemand mij helpen.

SoftAid · « **Reactie #1 Gepost op:** 31 oktober 2019, 17:45:38 »

Hallo Anjar,

Welkom op Oplossing.be

Er is wel iets mis met je bijlage, die is 0 kB en dus leeg.

Kan je het goede bestand aanhangen, aub?

Groeten,

SoftAid

anjar · « **Reactie #2 Gepost op:** 31 oktober 2019, 17:57:26 »

Poging3

popipipo · « **Reactie #3 Gepost op:** 31 oktober 2019, 20:11:07 »

Code: [Selecteer]

=VERT.ZOEKEN(B2;oud!$C$1:$F$15;4;0)&" "&VERT.ZOEKEN(E2;oud!$B:$F;5;0)Misschien zoiets?

SoftAid · « **Reactie #4 Gepost op:** 31 oktober 2019, 23:02:11 »

popipipo,

het zal mijns inziens toch ietsje meer moeten zijn dan:

Code: [Selecteer]

=VERT.ZOEKEN(B2;oud!$C$1:$F$15;4;0)&" "&VERT.ZOEKEN(E2;oud!$B:$F;5;0)
Met de formule:

Code: [Selecteer]

=ALS(EN(VERT.ZOEKEN($B2;oud!C:C;1;0);VERT.ZOEKEN(E2;oud!B:B;1;0);VERT.ZOEKEN(E2;oud!B:B;1;0));oud!F15;"") kom je al een stukje verder in de buurt, maar dan is ze nog alles behalve sluitend.

Ik ben er (haast) zeker van dat anjar wilt dat de waarden in 1 rij in het tabblad "oud" overeenkomen met de waarden in 1 rij van tabblad "nieuw", en er dan de waarde van diezelfde rij "oud" kolom F wordt overgebracht naar "Nieuw" kolom G.

Zelf zou ik dergelijk grote database niet met formules gaan bewerken, maar er een macro het werk laten doen.

Groeten,

SoftAid

emields · « **Reactie #5 Gepost op:** 31 oktober 2019, 23:23:06 »

ik denk dat hij eerder zoiets zoekt, zou waarschijnlijk met sorteren en een formule ook kunnen opgelost worden, maar heb het met een macro gedaan.

pitufo · « **Reactie #6 Gepost op:** 01 november 2019, 09:31:17 »

Hallo,

Waarom toch weer zo ver gaan zoeken?
Het gaat vermoedelijk om een eenmalige operatie en zal wellicht opgelost raken of al zijn.
Dus gewoon voor de aardigheid (alhoewel: misschien ook als algemeen ideetje voor andere projecten) hierbij ook nog een 'eenvoudige formule'-methode waarbij enkel een hulpkolommetje op 'oud' volstond.
Gaat trouwens zelfs sneller dan de macro-methode.

Groeten,
pitufo

SoftAid · « **Reactie #7 Gepost op:** 01 november 2019, 11:41:20 »

Hallo pitufo,

je methode met enkele formules lijken eenvoudig, maar deze zijn niet waterdicht.
Je vergelijkt 2 getallen die gegenereerd worden door de getallen in 3 kolommen van dezelfde rij achter elkaar te schrijven. Een beetje als Tekst.samenvoegen.

Maar... bij honderdduizenden rijen kan er voor verschillende (waarden in) rijen toch hetzelfde getal gegenereerd worden, en dan worden foute beschrijvingen gekozen om over te zetten naar kolom G van Nieuw.
vb: 301&1006&200 geeft 3011006200
30&11006&200 geeft 3011006200

Bij 20 rijen lijkt die kans minuscuul, maar bij een kwart miljoen rijen heb je kans om de lotto te winnen

De macro van emields heb ik uitvoerig getest, en die geeft (tot nu toe) geen fouten.

Groeten,

SoftAid

pitufo · « **Reactie #8 Gepost op:** 01 november 2019, 12:01:23 »

Citaat

je methode met enkele formules lijken eenvoudig, maar deze zijn niet waterdicht

Je hebt gelijk, SoftAid, (daarnet) niet goed uitgeslapen waarschijnlijk

Mits een kleine aanpassing kan de methode toch 100% waterdicht worden gemaakt, namelijk met een willekeurig karakter (bv. "/") tussen de elementen.
Op "oud": =B2&"/"&C2&"/"&E2
En op "nieuw" : =INDEX(oud!F:F;VERGELIJKEN(E2&"/"&B2&"/"&F2;oud!G:G;0))

Dank voor je oplettendheid

anjar · « **Reactie #9 Gepost op:** 01 november 2019, 15:10:59 »

Super bedankt voor het meedenken. Pitufo heeft gelijk, we denken te moeilijk en ik zocht de oplossing ook in excel met hele lastige moeilijke formules. De oplossing is wellicht ook te doen via power pivot functie van excel(die lijkt een beetje op acces). Het is nog niet in het grote bestand uitgevoerd, omdat ik vandaag een dagje vrij ben, maar de oplossing is volgens mij met acces te behalen.
Voor jullie heb ik deze nu ook bijgevoegd. Query 1 is de uitkomst, als je met je rechtermuisknop op ontwerpweergave gaat staan kun je zien dat de drie gewenste items aan elkaar geknoopt zijn en via openen, zie je de uitkomst, welke ik dan ook weer kan exporteren naar excel.

pitufo · « **Reactie #10 Gepost op:** 01 november 2019, 16:32:42 »

Oeps, anjar (Anja R.?), je hebt opnieuw een leeg bestand toegevoegd.

Nu ja, als het effectief om een eenmalig gebeuren gaat zou ik in jouw plaats niet de moeite nemen om een Access-omweg te gebruiken.
Ik had er ondertussen zelf nog even mee gespeeld, en een fictief bestandje met 100000 rijen gemaakt (correctie: door Excel láten maken!) en mijn methode loste het op in enkele seconden. De test om het met de vba-methode op te lossen heb ik wegens geduldgebrek maar opgegeven.
Vervolgens had ik de lijst terug gereduceerd tot 500 rijen en kwam tot 0.15 seconden met formule en 50 seconden met vba.
Wat ik wel zou adviseren als je het grote bestand aanpakt: zet eerst de formule in "oud", vervang de resultaten door waarden, en pas daarna de formule in "nieuw".

Voor extra duidelijkheid (in eerste instantie t.a.v. de andere helpers): het was met één oogopslag te zien dat de vba-code niet anders kon dan juiste resultaten opleveren, maar nét zo duidelijk dat het heel lang zou duren bij een groot aantal rijen.

Groeten,
pitufo

SoftAid · « **Reactie #11 Gepost op:** 01 november 2019, 18:22:13 »

Citaat van: pitufo op 01 november 2019, 16:32:42

Voor extra duidelijkheid:...... het was met één oogopslag te zien dat met de vba-code het heel lang zou duren bij een groot aantal rijen.

Ook als de code tussen:

Code: [Selecteer]

Application.ScreenUpdating = Trueen

Code: [Selecteer]

Application.ScreenUpdating = Falsestaat?

SoftAid

anjar · « **Reactie #12 Gepost op:** 01 november 2019, 18:59:17 »

Hopelijk komt hij nu wel goed over.

pitufo · « **Reactie #13 Gepost op:** 01 november 2019, 21:59:08 »

@ anjar,

Even je Access-bestand bekeken, en het enige wat je tot nu toe met die query hebt bereikt is dat de velden van plaats zijn verwisseld. Als je die in Excel importeert zullen daar dus je kolommen van plaats verwisseld zijn.
Als het evenwel zo is dat in "oud" elke rij zijn gelijke heeft in "nieuw" en vice versa, dan moest er hoegenaamd niets worden opgezocht en kon je toch gewoon de kolommen van plaats verwisselen (?)
In beide gevallen blijf ik bij mijn stelling dat de Access-tussenstap ook alleen maar extra werk met zicht brengt.

@ SoftAid

Citaat

Ook als de code tussen Application.ScreenUpdating = True en Application.ScreenUpdating = False staat?

Jawel ! Screenupdating heeft hier namelijk niet zo heel veel werk te verrichten. Het zal uiteraard altijd een verschil maken, maar de vba-code kan in dit geval onmogelijk qua snelheid de concurrentie aangaan met de formule-methode.
Anderzijds: voor wie het absoluut complex en traag wil maken... van mij mag het

Groeten,
pitufo

RedHead · « **Reactie #14 Gepost op:** 02 november 2019, 17:08:17 »

@Pitufo, doe mij 'ns een lol en wil je mijn code 'ns uitproberen op jouw dataset van 100.000 records?

Code: [Selecteer]

Sub OmsschrijvingZoeken()

    Nieuw = Sheets("nieuw").Cells(1).CurrentRegion.Resize(, 7)
    Oud = Sheets("oud").Cells(1).CurrentRegion
      
    Set Dict = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(Oud, 1)
        Dict.Add Join(Array(Oud(i, 3), Oud(i, 2), Oud(i, 5)), "|"), Oud(i, 6)
    Next i
    
    For i = 2 To UBound(Nieuw, 1)
        Nieuw(i, 7) = Join(Array(Nieuw(i, 2), Nieuw(i, 5), Nieuw(i, 6)), "|")
        Nieuw(i, 7) = Dict(Nieuw(i, 7))
    Next i
    
    Sheets("Tabel1").Cells(1).Resize(UBound(Nieuw, 1), UBound(Nieuw, 2)) = Nieuw
    
End Sub

Ik ben héél benieuwd hoelang deze er over doet.

Help!

Menu

Hulp bij posten

Zoeken

Recente topics