[excel2010]lijst genereren adhv match in matrixtabel

[fabstar81]

Hoi gotters

ik heb een lijst met deelnemers aan trainingen. je moet je voorstellen; kolom A, namen. vanaf kolom B alle trainingen. je krijgt zo een matrix met welke deelnemers welke trainingen volgen.

naam training1 traning2 training3

piet
klaas
marja
keestut
muts

nu wil ik per training onderaan elke kolom een lijstje maken met de deelnemers zodat je een overzichtje heb van de deelnemers. in de matrix maak ik gebruik van kruisjes "x" of "1" om de aanwezigheid te noteren.

is dit mogelijk? ik pieker me suf en heb het al met v- en hlookups geprobeerd, index, match en offsets, waarna concatenates, maar holy cow, ik schnap er nix van! Frequencies lukt ook niet erg, omdat ie dan alleen de unieke waardes optelt, maar deze kan je vervolgens niet afzonderlijk weer terug laten zoeken in de eerste namenkolom.

ik heb het met pivot tables geprobeerd, maar elke suggestie van matrix die ik selecteer, anders dan de default voorgestelde, accepteert ie niet.

lookups werken niet omdat de waardes in de training kolom allemaal hetzelfde zijn. een 1 (of x, nog niet gedefinieerd).

ps: ik heb de engelse excel 2010 beta

[F_J_K]

Je wilt dus een tabel hebben met
training1 - piet henk truus
training2 - henk truus miep
etc?

Niet mooi maar het werkt wel: een hulptabel maken met =ALS(B2="";"";$A2), oftewel: vervang de 1 door de naam. Voorbeeld met eerst de brontabel en ernaast na deze formule:

naam	t1	t2	t3	t1	t2	t3
piet	1		1	piet		piet
henk	1			henk		
truus	1	1		truus	truus	
foo		1	1		foo	foo
bar			1			bar

En dan eventueel transponeren.

[fabstar81]

ongeveer wel zo ja. ik was nu bezig om met een kopie hulptabel een telling te doen zodat ipv het kruisje een oplopend nummer komt bij een volgende naam die meedoet, de tussenliggende namen die niet meedoen nemen het nummer van de vorige over. zo creeer ik dus een uniek nummer voor iedereen die meedoet.

mbv VLOOKUP wilde ik dan de naam zoeken bij elke eerste instantie van het getal 1 tm 10. omdat er max 10 mensen mee mogen doen is de concatenate(vlookup1 tm 10) nog wel te doen.

de vlookup doet het echter nog niet, krijg hem niet aan de gang...

ik weet niet, is dit handiger of wat jij doet? transponeren ga ik me eens in verdiepen...

het probleem met jouw methode is dat je dus lege regels krijgt. hoe zet je dit dan weer om naar een aansluitende lijst van 10 personen?

[ Voor 9% gewijzigd door fabstar81 op 04-03-2010 09:48 ]

[pedorus]

Stel dat je de sheet hebt uit F_J_K's post, dan is de doortrekbare formule die op E2 moet staan, analoog aan deze formule:

code:

{=IF(ROW()-ROW(E$2:E$6)>=ROWS(B$2:B$6)-COUNTBLANK(B$2:B$6),"",
INDIRECT(ADDRESS(SMALL((IF(B$2:B$6<>"",ROW(B$2:B$6),ROW()+
ROWS(B$2:B$6))),ROW()-ROW(E$2:E$6)+1),COLUMN($A$2:$A$6),4)))}

(in te voeren met ctrl-shift-enter)

Deze formule is iets langer dan strikt noodzakelijk om consistentie te krijgen in de gebruikte referenties (bij row() en column()). Je begrijpt dat dit niet de eenvoudigste formule is.

[fabstar81]

ok, het is nu dit geworden:

code:

=IF(ISERROR(VLOOKUP($A44;INDIRECT(CONCATENATE("Lookup!"&ADDRESS(ROW()-
39;COLUMN()+C$43)&":"&ADDRESS(ROW()-
7;COLUMN()+C$43+3)));2;FALSE))=TRUE;"";VLOOKUP($A44;INDIRECT(CONCATENATE("Lookup!"&
ADDRESS(ROW()-39;COLUMN()+C$43)&":"&ADDRESS(ROW()-7;COLUMN()+C$43+3)));2;FALSE))

het moeilijkste was de formules relatief te maken zodat ik ze kon doortrekken. ik heb inderdaad een extra inleestabel gemaakt maar dan geen namen, maar toch nummertjes. daarna dezelfde nummers eruit filteren zodat je unieke nummers kreeg waarna een lookup volgt op het oorspronkelijke tabblad naar de nummers 1 tm 10.

omdat nu in de kopietabel 3 extra kolommen kwamen voor de nummering enzo, was het moeilijk de formule relatief te maken (elke kolom van waaruit gezocht moest worden, versprong telkens met een oplopend x-getal.)

een hoop addres()row()+ x enzo...

ik zal misschien ter illustratie de sheet even posten!! eerst de data eruit halen.
wat een gedoe zeg...

[fabstar81]

http://fabstar81.org/fabstar/Downloads/got.xlsx

daar staat ie