Zoeken van juiste celadres in steeds veranderende lijst/tabel

[Henk St.]

Goedendag,
Ik krijg iedere maand een dump uit een salarispakket in een lange Excellijst. De werknemers en de bijbehorende bedragen voor loon, premies etc. komen door maandelijkse wijzigingen steeds in een ander celadres in de lijst/tabel te staan te staan dan in de vorige dump (steeds wisselende lengte v.d. lijst). In kolom 1 staan de unieke werknemernummers, in kolom 8 alle bij de bewuste werknemer behorende bedragen onder elkaar. Zie m.n. de bijlage of hieronder.

VRAAG : met welke zoekformule kan ik iedere maand eerst de juiste werknemer vinden in kol. 1 (b.v. Piet) en daarna het juiste bijbehorende bedrag in de juiste rij in kol. 8 (b.v. premies = 35)

rij kol 1 kol. 6.(codes) kol. 7(omschr.) kol.8 (bedragen)
1 Jan 12005 loon 100
2 12006 premies 25
3 Piet 12005 loon 120
4 12006 premies 35

Via vert.zoeken, horiz. zoeken of index kom je wel in de juiste kolom, maar daarna niet in de juiste rij. Met =index(range;(vergelijken);kolom) lukte het ook niet. Het aantal kolommen blijft meestal wel gelijk, het aantal rijen nooit.

Vast reuze bedankt voor je oplossing.

Henk

 

[plagvreugd]

Hallo,

Ik hoop dat je xls-bestandje wel fake-data bevatte, want zomaar salarisinfo rondstrooien is natuurlijk nogal not done

Bijgevoegd een mogelijke oplossing. Ik heb eerst twee hulpkolommen toegevoegd aan het begin: eentje om voor elke rij het werknemernummer in te vullen, een tweede om het werknemernummer en de looncomponent aan elkaar te plakken. Vervolgens volstaat een verticale lookup.

Wellicht niet de mooiste oplossing, maar hij werkt wel

 

[HSV]

Zonder hulpkolom.

 

[WHER]

Nog een derde mogelijkheid

 

[Henk St.]

Beste Excelgenoten

Allen toppie bedankt voor jullie snelle oplossingen. Met alle drie aangereikte mogelijkheden kan ik zeker uit de voeten en ga ze subiet uittesten.
Twee vraagjes nog aan WEHR :
1. In de Help functie heb ik geprobeerd die zoekfunctie te doorgronden (vectorvariant?), maar kom er niet volledig uit. Wil je me svp jouw formule iets toelichten Staat in je formule het getal 2 voor de 2e rij onder de Koptekst en het getal 1 voor 1e kolom en vanwaar die deling/ backslash (somproduct) ?

2. Er is een truc om in een keer de lege cellen onder elk werknemernummer te vullen met dezelfde werknemernummers, weet jij nog stap voor hoe ? (ooit gezien op een congres).
b.v.
stap 1 : Eerst celeigenschap eerste kolom veranderen van tekst in getal ?
stap 2 e.v. F5 , Plakken speciaal, Ga naar, Lege waarden en dan ...Plakken speciaal, waarden
Krijg het helaas niet meer voor elkaar. Ff weten

Thanx vast

Henk

 

[plagvreugd]

Om je tweede punt te beantwoorden... Dit heb ik een paar dagen geleden ook op dit forum gezien.
1. Verander het Format van de cellen (in jouw oorspronkelijke bestand kolom A) van Text nar General.
2. Selecteer het bereik van cellen waar je de lege cellen wil veranderen (kolom A)
3. Kies CTRL + G
4. Klik op Special, kies Blanks, klik op OK
5. Typ =
6. Typ pijltje omhoog
7. Kies CTRL + ENTER.

Maar goed, dan moet je dat steeds handmatig doen (of in een macro zetten).

Grt,
plagvreugd

 

[WHER]

Henk,
=LOOKUP(2;1/(($A$3:$A$44=$L$17)*($F$3:$F$44=$L$18));$H$3:$H$44)


De 2 in de formule staat gewoon voor het getal 2, de 1 staat voor het getal 1. Het rode gedeelte resulteert via een reeks True/False's in een reeks 1'en en 0'en( één 1 en de rest 0'en). Het blauwe getal 1 wordt gedeeld door deze 1'en en 0'en, dit resulteert in een reeks van #DIV/0 foutmeldingen (gevolg van 1/0'en) met daartussen één 1 (gevolg van 1/1).
De lookup functie zoekt nu het getal 2 in deze reeks, vindt geen 2 en neemt vervolgens de dichtsbijzijnde waarde: 1. Aan de hand van de positie van deze 1 in dit gedeelte van de formule (de lookup vector) wordt op de overeenkomstige positie in de result vector (H3:H44)
de juiste waarde eruit gepikt.
Om dit proces te visualiseren kun je best de formule een paar keer doorlopen met de "evaluate" button uit de "formula auditing" toolbar.
Om dezelfde waarde in meerdere cellen tegelijk in te brengen, kun je ook als volgt te werk gaan: selecteer (bijvoorbeeld) A1:A20, typ je getal/woord in, bevestig met ctrl+enter.

 

[gelens]

Platvreugd,

De handeling is goed maar het zijn nog steeds formules. ALs je het bestand sorteer dan kunnen er rare dingen gebeuren. De desbetreffende cellen eerst nog "hard"inkopieeren.

Om je tweede punt te beantwoorden... Dit heb ik een paar dagen geleden ook op dit forum gezien.
1. Verander het Format van de cellen (in jouw oorspronkelijke bestand kolom A) van Text nar General.
2. Selecteer het bereik van cellen waar je de lege cellen wil veranderen (kolom A)
3. Kies CTRL + G
4. Klik op Special, kies Blanks, klik op OK
5. Typ =
6. Typ pijltje omhoog
7. Kies CTRL + ENTER.

Maar goed, dan moet je dat steeds handmatig doen (of in een macro zetten).

Grt,
plagvreugd

 

[dHijlkema]

De oplossing van HSV is nog altijd het mooist. :thumb: Hier hoef je niet overal de waarde in kolom A door te kopieren om het gewenste resultaat te krijgen.

Mocht je nou toch in kolom A de waarde doorkopieren, kan je ook met de index formule uit de voeten, zoals je zelf in je eerste post vermelde (doet feitelijk hetzelfde als de functie ZOEKEN):

{=INDEX($H$2:$H$44;VERGELIJKEN($L$17&$L$18;$A$2:$A$44&$F$2:$F$44;0))}

Groeten,
Dennis

 

[Henk St.]

Beste Excellers,

Thanx voor alle bijdragen. Heb alle zoekformules uitgetest en ze komen alle van pas. Dit scheelt voortaan mega tijd. Voor dit moment kies ik ervoor om toch eerst de kolom A geheel te vullen, dit voor het geval ik de dump later nog zou willen/moeten sorteren.

Vraag dus puik opgelost

groet
Henk