Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

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

0 leden en 1 gast bekijken dit topic.

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 1.886
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: combineren van verticaal zoeken
« Reactie #15 Gepost op: 02 november 2019, 21:58:59 »
Deze is nog net iets veiliger en 1 regel korter...  ;)
Sub OmschrijvingZoeken()

    tijd = Timer
   
    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(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) = Dict(Join(Array(Nieuw(i, 2), Nieuw(i, 5), Nieuw(i, 6)), "|"))
    Next i
   
    Sheets("Tabel1").Cells(1).Resize(UBound(Nieuw, 1), UBound(Nieuw, 2)) = Nieuw
   
    MsgBox "het proces duurde " & Timer - tijd & " seconden..."
   
End Sub
Bij het vullen van de Dictionary in de eerste lus spreek ik 'm nu rechtstreeks aan ipv rst via Add een Key toe te voegen. Als de Key al bestaat, heb je de kans dat de boel klapt. Met deze directe methode gebeurt dat niet maar wordt simpelweg de Value van de Key overschreven.
Daarnaast in de 2e lus het vullen - voor hergebruik - van de variabele overgeslagen.

[EDIT] en als je het voor mij wilt meten @Pitufo, dan heb ik de code net nog maar even verrijkt met een timer.  ;)
« Laatst bewerkt op: 02 november 2019, 22:10:47 door RedHead »
______________________________

Groet, Leo

Offline pitufo

  • Excel-Expert
  • Oplosser
  • *****
  • Berichten: 965
  • Geslacht: Man
Re: combineren van verticaal zoeken
« Reactie #16 Gepost op: 03 november 2019, 09:28:03 »
Hallo RedHead,

Ik heb toch nergens gensinueerd, laat staan gezegd, dat vba geen waardig alternatief kan zijn om deze kwestie op te lossen ?? Ik heb enkel gezegd dat de door emields gepubliceerde code ontzettend traag is, n dat er een doodeenvoudige en snelle methode zonder vba en zonder overmatig hersengebruik kon gevonden worden.
Ja hoor, ik gebruik zelf ook vba als het beter of gewoonweg noodzakelijk is.
Maar ik doe niet mee aan het vaak geziene "waarom het makkelijk maken als het ook moeilijk kan".

We kunnen hier natuurlijk nog een lang academisch debat over voeren maar wordt TS daar beter van?
Daarom was ik ook helemaal niet van plan de code te posten die ik zelf gisteren (louter voor mijn eigen amusement dus) nog had geschreven. Meer zelfs, ik had 2 versies: een eerste die vl sneller was dan die van emields en een tweede die nogal op mijn formule-methode leek, m.a.w. een code die door chte experts wel zal afgeschoten worden.

Mijn eerste was (ik geef het hl graag toe) nog steeds een stuk trager dan die van jou, maar de tweede is een beetje sneller.
Ik heb het niet getest op een dataset van 100000 maar van 270000 (zie post 1) records. Mijn traag pc'tje (op een snellere zullen de verhoudingen wel blijven kloppen zeker?) gaf 24,25 seconden met jouw methode en 18,8 met de mijne (een verschil uiteraard om niet echt van wakker te liggen!)

Ik speel (half gedwongen) dus ook nog even mee:
Sub pitufo()

begin = Timer
Application.ScreenUpdating = False
With Sheets("oud")
    .[e2].Formula = "=a2&""/""&b2&""/""&c2"
    .[e2].Copy .Range("e3:e" & .Cells(Rows.Count, 1).End(xlUp).Row)
    .Columns(5) = .Columns(5).Value
End With
With Sheets("nieuw")
    .[d2].Formula = "=index(oud!d:d,(match(b2&""/""&a2&""/""&c2,oud!e:e,0)))"
    .[d2].Copy .Range("d3:d" & .Cells(Rows.Count, 1).End(xlUp).Row)
    .Columns(4) = .Columns(4).Value
End With
Sheets("oud").Columns(5).Delete
Application.CutCopyMode = False
MsgBox (Timer - begin)

End Sub

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 17.735
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: combineren van verticaal zoeken
« Reactie #17 Gepost op: 03 november 2019, 14:10:00 »
veel wedijver en afgunst merk ik hier :-[.

Voor een (zeer waarschijnlijk) eenmalig uit te voeren bewerking steekt het toch niet op enkele tientallen seconden meer of minder, denk ik zo.... ::)

Laat het plezant blijven, en geef het beste van jezelf, zonder een andere helper de loef proberen af te steken.

Het is echt geen wedstrijd, hier....

Thanks  ;) :)

:) SoftAid :)             

Wegens veelvuldig melden van dubbelposten, het posten op verschillende help-fora van dezelfde vraag, zijn de gedragsregels op dit forum aangepast. Dubbelposten dient in de toekomst op voorhand gemeld te worden, met een melding en een link naar het topic op de andere site. De helpers wensen een weinig respect voor de inspanningen die ze doen om mensen te helpen. Je doet jezelf ook een plezier. Door je dubbelposten te melden wordt je vraag mogelijk op meerdere fora beantwoord.

Offline pitufo

  • Excel-Expert
  • Oplosser
  • *****
  • Berichten: 965
  • Geslacht: Man
Re: combineren van verticaal zoeken
« Reactie #18 Gepost op: 03 november 2019, 16:46:26 »
Citaat
Voor een (zeer waarschijnlijk) eenmalig uit te voeren bewerking steekt het toch niet op enkele tientallen seconden
Net wat ik zelf meerdere posts geleden al heb gezegd, namelijk "doe dit toch gewoon met een formule" maar dat was volgens velen niet goed genoeg...

Citaat
veel wedijver en afgunst merk ik hier
... en het gevolg is dat er even wordt op doorgegaan. Het valt blijkbaar niet altijd in te schatten hoe dat door anderen zal worden aangevoeld. Voor mij was dat tot nu evenwel noch meer noch minder dan een plezante stimulans om, als variant op het begrip "off topic", nog een beetje van gedachten te wisselen.
Moeilijke mensen (waartoe ik in alle bescheidenheid meen niet te behoren) zouden zelfs kunnen stellen dat net een dergelijke sneer het plezante karakter teniet doet...

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 1.886
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: combineren van verticaal zoeken
« Reactie #19 Gepost op: 03 november 2019, 20:46:55 »
Ik heb toch nergens gensinueerd, laat staan gezegd, dat vba geen waardig alternatief kan zijn om deze kwestie op te lossen ??
Wowwww... wr komt dit vandaan? Ik k niet toch? Ook heb ik jou nergens in aangevallen? Zie hier de ellende soms van het geschreven woord... Er kunnen dingen opgemaakt worden die nooit gezegd zijn maar misschien op een bepaalde wijze kunnen worden opgevat.
De reden dat ik de VBA optie even verder had uitgewerkt komt door het "leer gedeelte" waar ik nu zelf in zit. Het werken met Dictionaries is een hl nieuw hoofdstuk voor mij. Dit was nu een uitgelezen voorbeeld voor mij om uit te werken.
Op die paar records in het workbook van de TS had mijn procedure uiteraard geen enkel probleem. Maar omdat jij had aangegeven dat je een testworkbook met 100.000 regels had gemaakt, leek 't mij handig als je die voor mij even kon inzetten om mijn procedrue te timen. Zo hoefde ik niet zelf een testworkbook in elkaar te draaien. En was mijn vraag dus niet om elkaar eventueel de loef af te steken, maar puur voor mijn eigen "lering ende vermaek".

Ik zie het daarnast op fora regelmatig gebeuren dat een TS al geholpen is met een oplossing, maar dat helpers elkaar helpen op een hoger niveau te komen en dat een draadje nog even "doorsuddert". Dt lijkt mij persoonlijk een heel goede zaak. Ik help heel graag, maar mijn helpen zie ik dus ook als mogelijkheid om mezelf weer te verbeteren.

Dat gezegd hebbende... Nu weer terug naar het draadje.  ;)
Ik vind het heel gaaf om te zien dat een oplossing met het plaatsen van formules in de worksheet dan toch nog sneller is dan een Dictionary. Die had ik persoonlijk niet verwacht na het bestuderen van deze materie. Dank dus voor het testen van mijn procedure en het plaatsen van die van jou.
______________________________

Groet, Leo

Offline pitufo

  • Excel-Expert
  • Oplosser
  • *****
  • Berichten: 965
  • Geslacht: Man
Re: combineren van verticaal zoeken
« Reactie #20 Gepost op: 04 november 2019, 18:55:26 »
Hallo RedHead,

Maar ik ben het volkomen met je eens! Als ik zou moeten omschrijven hoe ik zelf de dingen benader kan ik beter gewoon je tekst kopiren.
Vanzelfsprekend geldt dat ook voor
Citaat
Ik zie het daarnast op fora regelmatig gebeuren dat een TS al geholpen is met een oplossing, maar dat helpers elkaar helpen op een hoger niveau te komen en dat een draadje nog even "doorsuddert". Dt lijkt mij persoonlijk een heel goede zaak
Een hl goede zaak!

En ook met deze voel ik mij verwant
Citaat
Zie hier de ellende soms van het geschreven woord... Er kunnen dingen opgemaakt worden die nooit gezegd zijn maar misschien op een bepaalde wijze kunnen worden opgevat
Ik kom dat ook op regelmatige basis tegen, zowel aan het ene als aan het andere eind van de kwestie!
In concreto: hoogstens zag ik jouw posts ten onrechte als een soort uitdaging, maar dan wel een leuke, anders was ik er niet op ingegaan. Maar aan de andere kant was mijn reactie ook hoegenaamd niet als tegenaanval bedoeld (zoals jij misschien hebt gedacht?)

Nu ja, er zijn nuttiger en interessanter zaken om over te praten. Toevallig ben ik ook sinds heel kort aan Dictionaries begonnen maar mee werken durf ik het nog niet noemen, eerst nog maar wat beter uitpluizen lijkt mij aangewezen...

Wat ik tegenwoordig veel doe is sql implementeren in vba. Het grote voordeel is dat je basisgegevens dan zowat overal mogen staan, ook buiten Excel dus. Neem me niet kwalijk als het iets is wat je ook al lang hanteert. In het andere geval hoop ik ten zeerste dat je hier misschien iets aan hebt. Om het dan even bij dit topic te houden, dat zou ik dus zo doen:
Sub sql_methode()
   
bron = ActiveWorkbook.Path & "\origineel.xlsx"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="odbc;dsn=excel files;dbq=" & bron, Destination:=Range("$A$1")).QueryTable
    .CommandText = "select [nieuw$].*, [oud$].omschrijving from [nieuw$] " _
    & "inner join [oud$] on [nieuw$].bedrag = [oud$].bedrag " _
    & "and [nieuw$].kostensoort = [oud$].kostensoort " _
    & "and [nieuw$].periode = [oud$].periode " _
    & "order by [nieuw$].kostensoort"
    .Refresh BackgroundQuery:=False
End With

End Sub
Deze code staat dus in een leeg werkboek en de gegevens in "origineel.xlsx" (in het voorbeeld beide in dezelfde map maar hoeft uiteraard niet).
De snelheid zit in dezelfde grootteorde maar in andere situaties zoals (delen van) grote databases uitlezen bewijst het pas zijn grote nut.

Maar zoals gezegd: please don't shoot the piano player als ik je niets nieuws vertel  0:-)

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline RedHead

  • Excel-Expert
  • Ambassadeur
  • *****
  • Berichten: 1.886
  • Geslacht: Man
  • Met Excel lukt 't wel.... (toch???)
Re: combineren van verticaal zoeken
« Reactie #21 Gepost op: 04 november 2019, 20:28:47 »
Pitufo, fijn dat de neuzen weer dezelfde kant uit staan.  ;) ;D

Werken met SQL in VBA heb ik nog nooit gedaan. Leuk om ook 'ns beet te pakken. Tnx voor je voorbeeld.
Als jij ook nieuw bent met Dictionaries, kan ik je van harte deze Guru aanbevelen. Kwam ik bij toeval tegen op Youtube, maar heeft een hele fijn stijl van uitleggen. Wellicht kan jij (of een andere lezer) hier nog zijn of haar voordeel mee doen.
Daarnaast kan een oplossing in zowel formules als VBA bij een vraagstuk ook meer verkeer generen op Oplossing.BE. Is k weer leuk voor SoftAid.  8) Als ik bijvoorbeeld Formule Guru Mike Girvin zie, dan geloof je bijna niet wt er allemaal zonder VBA kan. Deze MVP heeft er een ware strijd van gemaakt om de meest lastige vraagstukken op te lossen met formules. Zeer leerzaam om ook die pagina's 'ns te bekijken op Youtube.
______________________________

Groet, Leo

Offline pitufo

  • Excel-Expert
  • Oplosser
  • *****
  • Berichten: 965
  • Geslacht: Man
Re: combineren van verticaal zoeken
« Reactie #22 Gepost op: 05 november 2019, 12:57:54 »
Hoi RedHead,

Citaat
fijn dat de neuzen weer dezelfde kant uit staan
O maar dat is wat mij betreft (ik kan je natuurlijk enkel via figuurlijke digitale weg in de ogen kijken terwijl ik dit zeg) nooit anders geweest!

Dank voor de Dictionaries- en andere link. Het zal heus niet lang duren voor ik erin vlieg.

Leuk dat ik jou ook op een mogelijk nieuw spoor heb kunnen zetten. Veel nut had mijn voorbeeld natuurlijk niet, het was hoogstens een aangeven van een mogelijkheid. Ik krijg in mijn job te maken met gigantische databases waarvan sommige uit honderden tabellen bestaan, waarbinnen dan weer sommige met 2 records, andere met enkele miljoenen. Er op deze wijze gewoon je verlangde gegevens kunnen uitplukken laat een nieuwe wereld opengaan, althans zo heb ik het ervaren toen ik dat ging proberen. Inderdaad 'proberen', want mijn eerste stap was... een macro opnemen. Die ziet er afschuwelijk uit, maar met wat trial and error bij de opkuis kom je een eind.

Als je zin zou hebben om het wat te gaan exploreren: links erover heb ik zo niet direct maar in https://www.oplossing.be/excel/herstellen-excel-bestand/ is er al wat uitgebreider op ingegaan. Een beetje geduld bij het doorworstelen van dat draadje is dan wel aangewezen...

Veel "lering ende vermaek" gewenst!

Groeten,
pitufo
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

Offline Georgyboy

  • Volledig lid
  • **
  • Berichten: 157
  • Geslacht: Man
  • Oplossing.be
Re: combineren van verticaal zoeken
« Reactie #23 Gepost op: 09 november 2019, 11:30:21 »
Hey Putifo,

Herken dit :)

https://www.oplossing.be/excel/herstellen-excel-bestand

Bedankt nogmaals voor al je hulp en toelichtingen. Je hebt er zr vl tijd voor genomen.
Ben blij dat deze vraag door jouw oplossingen ook meerdere personen kan helpen, dus denk ik dat het de moeite loonde deze vaag te stellen. :)

Groetjes
Georgyboy

MOD-ified: linken niet tussen code zetten maakt ze aanklikbaar  ;)

Offline pitufo

  • Excel-Expert
  • Oplosser
  • *****
  • Berichten: 965
  • Geslacht: Man
Re: combineren van verticaal zoeken
« Reactie #24 Gepost op: 09 november 2019, 11:42:58 »
Er zijn maar hl weinig vragen die niet de moeite lonen om gesteld te worden...  :D
"De computer doet wel degelijk wat je hem vraagt,
 maar NIET wat je DENKT dat je hem vraagt"

 


www.combell.com