[Excel 2013] Automatisch appenden van kolommen in één kolom

[YellowOnline]

Ik wil iets doen dat mij niet zo exotisch lijkt, maar ik vind niet meteen een methode.

Gesteld:

	A	B	C	D	E	F
1	2011		2012		2013
2	Alpha	1	Delta	2	Alpha	3
3	Bravo	2	Echo	1	Foxtrot	3
4	Charlie	1	Delta	2	Alpha	2

Ik wil automatisch kolommen G en H creëren met:

	G	H
1	Alpha	6
2	Bravo	2
3	Charlie	1
4	Delta	4
5	Echo	1
6	Foxtrot	3

De nadruk ligt op automatisch: de data in de kolommen verandert elke maand en ik (enfin, de gebruiker) weet ook niet hoe lang die kolom wordt. Op basis van de laatste twee kolommen wordt een pie-chart gegenereerd (waar ik met hetzelfde probleem kamp overigens...charts aanvaarden geen INDIRECT).

Opzoekingen op Google met merge of append brengen mij steeds bij concatenates en dat is natuurlijk niet wat ik hier wil doen ...

Een enkele kolom (nou ja, heb een grens gesteld op 200) laten dupliceren kan ik als volgt (met echte coördinaten, niet dummy van hierboven):

code:

=IFERROR(INDEX($AE$40:$AE$240,SMALL((IF(LEN($AE$40:$AE$240),ROW(INDIRECT("1:"&ROWS($AE$40:$AE$240))))),ROW(A1)),1),"")

Maar ook hier zit ik vast: geen flauw benul hoe ik op ISBLANK kan overschakelen naar de volgende kolom (in casu AH). Goed in ieder geval dat het aantal kolommen voorspelbaar is (12 - of eigenlijk 24, 2 per maand, waarvan één met tekst en één met cijfers).

[ Voor 17% gewijzigd door YellowOnline op 17-03-2015 13:50 ]

[naitsoezn]

Ik begrijp niet zo goed wat er in kolom G / H moet staan?

Misschien dat je bedoelt om van een matrix een vector te maken? Dat kan met OFFSET.

[ Voor 92% gewijzigd door naitsoezn op 17-03-2015 13:48 ]

[Fiber]

naitsoezn schreef op dinsdag 17 maart 2015 @ 13:44:
Ik begrijp niet zo goed wat er in kolom G / H moet staan?

[...]

Ongetelde waardes van Alpha, Bravo, etc...

[naitsoezn]

Fiber schreef op dinsdag 17 maart 2015 @ 13:47:
[...]

Ongetelde waardes van Alpha, Bravo, etc...

Met SUMIF kun je alle getallen optellen die aan een conditie voldoen. Zo kun je bijvoorbeeld in jouw voorbeeld gebruiken:
=SUMIF(A1:E4, "=Alpha", B1:F4)
om op 6 in kolom H te komen

Of, algemener, in cell H1 zet je dan:
=SUMIF(A1:E4, "="&G1, B1:F4)

Hoef je 'alleen' nog maar de unieke identifiers in kolom G weten te krijgen.

[ Voor 17% gewijzigd door naitsoezn op 17-03-2015 13:52 ]

[YellowOnline]

naitsoezn schreef op dinsdag 17 maart 2015 @ 13:49:
[...]


Met SUMIF kun je alle getallen optellen die aan een conditie voldoen. Zo kun je bijvoorbeeld in jouw voorbeeld gebruiken:
=SUMIF(A1:E4, "=Alpha", B1:F4)
om op 6 in kolom G te komen

Maar ik weet niet van het bestaan van alpha, bravo of wat dan ook. Misschien moet ik even uitleggen wat dit spreadsheet concreet doet.

Per maand is er een kolom waarop een totaal komt van de tijd die ze aan elk project hebben besteed. Iets zoals dit, bijvoorbeeld:

Januari
Coca-Cola 50 uur
Volkswagen 20 uur
Apple 35 uur

December
Coca-Cola 20 uur
McDonalds 10 uur
Tweakers 35 uur

De bedoeling is nu een aggregaat te maken van die gegevens. Met bovenstaande voorbeeld zou dat er als volgt uitzien:

2015
Coca-Cola 70 uur
Volkswagen 20 uur
Apple 35 uur
McDonalds 10 uur
Tweakers 35 uur

Belangrijk te weten is dus dat
1) Het aantal kolommen constant is (2 per maand)
2) Het aantal rijen variabel is
3) De waarden voor elke maand zowel identiek als anders kunnen zijn

Omwille van dat laatste is een tussenkolom met dubbels vermoedelijk onvermijdelijk.

Edit:

naitsoezn schreef op dinsdag 17 maart 2015 @ 13:49:
[...]
Hoef je 'alleen' nog maar de unieke identifiers in kolom G weten te krijgen.

Ja, dat is dan ook de reden van mijn post Ik zou een VBA oplossing in elkaar kunnen flansen, maar omdat unsigned macro's in vele omgevingen een probleem zijn moet ik mij tot formules beperken.

---

Disclaimer: Dit is voor een non-profitorganisatie en ik doe het geheel vrijwillig en onbezoldigd. Bovenstaande voorbeeldfirma's zijn fictief.

[ Voor 17% gewijzigd door YellowOnline op 17-03-2015 14:04 ]

[naitsoezn]

YellowOnline schreef op dinsdag 17 maart 2015 @ 13:57:
[...]

1) Het aantal kolommen constant is (2 per maand)

Als je alle unieke entries uit één kolom wilt krijgen kun je gebruik maken van een Array-functie. Bijvoorbeeld:
Als kolom B de data bevat met de strings, bijvoorbeeld in de range B2:B15, en in G wil je alle unieke entries, dan zet je in cell G2 het volgende statement:

=IFERROR(INDEX($B$2:$B$15, MATCH(0,COUNTIF($G$1:G1, $B$2:$B$15), 0)),"")

Dan moet je op Ctrl+Shift+Enter drukken (heel belangrijk bij Array-functies), en de cell G2 kun je vervolgens naar beneden kopieren om alle unieke entries te krijgen. De SUMIF van m'n eerdere post kun je dan gebruiken om de getallen in H te krijgen.

Ja, dat is dan ook de reden van mijn post

Zeg dat dan direct

[YellowOnline]

naitsoezn schreef op dinsdag 17 maart 2015 @ 14:11:
[...]

Als je alle unieke entries uit één kolom wilt krijgen kun je gebruik maken van een Array-functie.
[...]

Binnen een enkele kolom zijn ze al uniek - zo'n array heb ik al gemaakt op het worksheet waar de data vandaan komt.

tl;dr
Het probleem is het geautomatiseerde samenvoegen van verschillende kolommen van onbekende lengte.

Zeg niet dat ik het nu niet direct gezegd heb

---

Ik heb plots een idee voor de dynamiek: bepalen wat de laatst gebruikte rij is.

code:

=MAX((A40:A240<>"")*(ROW(A40:A240)))

Dit kan gebruikt worden in de formule die ranges samenvoegt. Nu die formule nog vinden -.-

[ Voor 18% gewijzigd door YellowOnline op 17-03-2015 14:18 ]

[naitsoezn]

Moet het per se in één formule (of mogen er ook hulp-kolommen gebruikt worden) en zijn het aantal kolommen altijd gelijk?

Edit: Als je geen headers in je data hebt, kun je A:A gebruiken om alle elementen uit kolom A mee aan te duiden. Voor als je niet weet hoeveel cellen data bevatten.

Edit-edit: Die formule om ranges mee samen te voegen moet ik je toch weer naar OFFSET verwijzen.

[ Voor 55% gewijzigd door naitsoezn op 17-03-2015 14:27 ]

[YellowOnline]

naitsoezn schreef op dinsdag 17 maart 2015 @ 14:24:
Moet het per se in één formule (of mogen er ook hulp-kolommen gebruikt worden) en zijn het aantal kolommen altijd gelijk?

Edit: Als je geen headers in je data hebt, kun je A:A gebruiken om alle elementen uit kolom A mee aan te duiden. Voor als je niet weet hoeveel cellen data bevatten.

Het aantal kolommen is altijd gelijk: 2 per maand (eentje met namen en eentje met uren). Hulpkolommen zijn geen probleem, aangezien ik die toch hide en lock.

Edit: De eerste 40 rijen staan vol met andere data (... en de bijhorende pie chart), vandaar dat ik geen A:A kan doen helaas. Maar goed, in de oude informaticatraditie ga ik er van uit dat 200 rijen data voldoende is.

"Assumption is the mother of all fuckups"... hmz, niemand zei dat het een goeie traditie was.

[ Voor 23% gewijzigd door YellowOnline op 17-03-2015 14:29 ]

[naitsoezn]

YellowOnline schreef op dinsdag 17 maart 2015 @ 14:27:
[...]


Het aantal kolommen is altijd gelijk: 2 per maand (eentje met namen en eentje met uren). Hulpkolommen zijn geen probleem, aangezien ik die toch hide en lock.

Ja, 2 per maand, maar ook altijd hetzelfde aantal maanden? Je wilde toch meerdere kolommen met namen in één stoppen? Altijd hetzelfde aantal kolommen met namen of varieert dit?

[Belindo]

Als je je data aan kan/mag passen, zet het dan in een tabel zoals:

	A	B	C	D
1	Onderwerp	Jaar	Maand	Aantal Uren
2	Alpha	2014	2	48
3	Beta	2015	6	12
4	Echo	2014	1	32
5	Echo	2015	12	56

Dan hoef je voor je rapporten alleen nog een simpele pivottable toe te voegen.

Edit: Plaatje voor de duidelijkheid

[ Voor 6% gewijzigd door Belindo op 17-03-2015 14:41 ]

[YellowOnline]

naitsoezn schreef op dinsdag 17 maart 2015 @ 14:29:
[...]

Ja, 2 per maand, maar ook altijd hetzelfde aantal maanden? Je wilde toch meerdere kolommen met namen in één stoppen? Altijd hetzelfde aantal kolommen met namen of varieert dit?

De dag Het jaar dat in een (westerse) kalender er meer of minder dan 12 maanden zijn heb ik andere zorgen dat een spreadsheet

Serieus: het is telkens één workbook per jaar, dus ook telkens 12 maanden per jaar. In het echt zijn er drie kolommen per maand, maar één van die drie speelt geen rol in de nieuwe kolom. Het ziet er in praktijk zo uit (maar dan met veel meer data en voor 12 ipv. 3 maanden, en met de maandnamen over 3 kolommen gemerged, iets dat ik niet met BBcode kan tot zover ik weet):

	A	B	C	D	E	F	G	H	I
1	Januari			Februari			Maart
2	Naam	Uren	Procent	Naam	Uren	Procent	Naam	Uren	Procent
3	Alpha	10	20%	Delta	20	40%	Alpha	5	10%
4	Bravo	20	40%	Echo	10	20%	Foxtrot	25	50%
5	Charlie	10	20%	Foxtrot	20	40%	Golf	20	40%

Belindo schreef op dinsdag 17 maart 2015 @ 14:36:
Als je je data aan kan/mag passen, zet het dan in een tabel zoals:
[...]
Dan hoef je voor je rapporten alleen nog een simpele pivottable toe te voegen.

Edit: Plaatje voor de duidelijkheid
[afbeelding]

Hmz, ik moet hier even naar kijken.

[ Voor 9% gewijzigd door YellowOnline op 17-03-2015 14:46 ]

[Fiber]

Met Belindo, de opbouw van je data is gewoon niet handig. De opbouw van Belindo is veel handiger. Ik zou zelfs de ruwe data in een andere sheet zetten dan de mooie samenvattingen. Desnoods maak je een aparte sheet voor de ruwe data van Alpha, Bravo, etc.

[YellowOnline]

Die opbouw is wél handig voor het menselijke oog dat in één oogopslag wil zien wat er op maandelijkse basis gebeurd. Die data is ook niet ingegeven op die plaats: die wordt opgehaald op aparte work sheets (1 per maand). M.a.w., de data reorganiseren zoals in Belindo's voorbeeld gaat helemaal niet. Ik zal m'n bestand eens posten. Misschien is het allemaal duidelijker als jullie het echte bestand zien. De beige velden zijn normaal gezien gelocked, de rode velden hidden. Alle maanden zijn leeg, behalve november en december, om te testen.

[ Voor 28% gewijzigd door YellowOnline op 17-03-2015 15:06 ]

[naitsoezn]

Dit volgende commando gaat er vanuit dat de data in de range A40:AJ240 staat, waarbij de data uit elke derde kolom onder elkaar geplakt wordt:
=OFFSET($A$40:$AJ$240 ,MOD(ROW()-ROW($AL$2),ROWS($A$40:$AJ$240 )),3*TRUNC((ROW()-ROW($AL$2))/ROWS($A$40:$AJ$240 )),1,1)

Deze formule moet je dan stoppen in cell AL2; als je hem liever in een andere kolom stopt waar je tijdelijke data kwijt kunt, dan moet je in die formule de cell $AL$2 vervangen door de betreffende cell.

Vervolgens kopieer je deze cell helemaal naar beneden en krijg je alle entries onder elkaar (lege cellen worden een 0). Nu kun je de eerder genoemde Array-functie gebruiken om deze kolom weer te ontdoen van alle duplicates (het verwijderen van de ene unieke 0 die overblijft laat ik als opgave voor de lezer )

[YellowOnline]

naitsoezn schreef op dinsdag 17 maart 2015 @ 15:24:
Dit volgende commando gaat er vanuit dat de data in de range A40:AJ240 staat, waarbij de data uit elke derde kolom onder elkaar geplakt wordt:
=OFFSET($A$40:$AJ$240 ,MOD(ROW()-ROW($AL$2),ROWS($A$40:$AJ$240 )),3*TRUNC((ROW()-ROW($AL$2))/ROWS($A$40:$AJ$240 )),1,1)

Deze formule moet je dan stoppen in cell AL2; als je hem liever in een andere kolom stopt waar je tijdelijke data kwijt kunt, dan moet je in die formule de cell $AL$2 vervangen door de betreffende cell.

Vervolgens kopieer je deze cell helemaal naar beneden en krijg je alle entries onder elkaar (lege cellen worden een 0). Nu kun je de eerder genoemde Array-functie gebruiken om deze kolom weer te ontdoen van alle duplicates (het verwijderen van de ene unieke 0 die overblijft laat ik als opgave voor de lezer )

AL2 was inderdaad gereserveerd voor dit doeleinde.

Dit ziet er interessant uit. De magie zit 'm in TRUNC blijkbaar. Even mee prutsen, want een eerste test geeft gewoon blanco cellen. OK, I'm stupid, ik krijg natuurlijk eerst enkele 1000en blanco cellen... verder doortrekken

[ Voor 5% gewijzigd door YellowOnline op 17-03-2015 15:39 ]