[Excel 2003] Transponeren met herhaald uniek gegeven (ID)

[brose]

Ik heb een excel sheet met daarin in de eerste kolommen de adres gegevens van
contactpersoenen en daarnaast 6 kolommen met giften per jaar (2009 t/m 2005)
van deze contactpersoon.

ID | Naam | Adres | Plaats | Gift 2009 | Gift 2008 | Gift 2007 | Gift 2006

Voorbeeld:

1 | Jan | Zeeweg 1 | Den Haag | 22,50 | 15,00 | 80,00 | 25,00
2 | Niek | Bol 3 | Haarlem | 23,00 | 5,00 | 70,00 | 50,00


Voor een import (de import applicatie leest de gegevens regel per regel
(niet per kolom) moet ik deze gegevens transponeren naar:

ID | Jaar Gift | Bedrag

Met als voorbeeld

1 | 2006 | 25,00
1 | 2007 | 80,00
1 | 2008 | 15,00
1 | 2009 | 22,50
2 | 2006 | 50,00
2 | 2007 | 70,00
2 | 2008 | 5,00
2 | 2009 | 23,00

Ik wil dus enkel de laatste velden transponeren en in het eerste veld het
unieke ID herhalen. Hoe kan ik dit voor elkaar krijgen?

Alvast dank!

[Verwijderd]

Ik begrijp uit het voorbeeld dat (de lengte van) de eerste kolom bekend is (4 items per persoon)? En zo ja dan is kolom 2 ook bekend.

In dat geval in C1 (1e cel kolom 3):

=index(voorbeeld, A1 (verticale verschuiving), -2003 (ongeveer) + B1 (hor. versch.), false)

Voorbeeld is de array met data.

[ Voor 10% gewijzigd door Verwijderd op 05-03-2009 17:58 ]

[brose]

Ik heb hem inmiddels teruggebracht tot de Unique ID's na een losse import van de contactpersoon gegevens (de eerste kolommen). Ik zit nu met de volgende xls:

IMPORT sugar_id | 2008 | 2007 | 2006 | 2005
2d900a86-cca6-ced1-ccc8-49affb6718f6 | 10 | 10 | 10 | 10

Die dus zo moet worden:

IMPORT sugar_id Jaar Bedrag
2d900a86-cca6-ced1-ccc8-49affb6718f6 2008 10
2d900a86-cca6-ced1-ccc8-49affb6718f6 2007 10
2d900a86-cca6-ced1-ccc8-49affb6718f6 2006 10
2d900a86-cca6-ced1-ccc8-49affb6718f6 2005 10

Klopt je suggestie dan nog?

[Verwijderd]

Ja, verhaal blijft dan geliijk. Index() kan namelijk ook tekst aan.

[onkl]

Je hebt drie opties, van moeilijk maken en makkelijk in het gebruik naar makkelijk te maken, maar bewerkelijker in het gebruik.

De moeilijkste setup is een vba macro wat door je data heenloopt en een tekststream wegschrijft. Anderszijds: het gebruiken ervan is "druk op knopje. Klaar."

De oplossing van grizzlybeer is in de meeste gevallen een uitstekende middenweg. 't is even prutsen, maar dan heb je iets wat goed werkt. En zolang je zorgt dat mensen zonder hersens er vanaf blijven, blijft het ook goed werken.

De eenvoudigste oplossing: Kopieer de data vier keer, voeg een kolom met een jaarnummer in en flikker per lijstje drie jaren weg, totdat je vier lijstjes hebt met id, jaar en waarde, voor ieder jaar 1.
Zet de lijstje onder elkaar en sorteer het geheel op ID. Klaar. En ideaal als je dit drie keer per jaar moet doen. Maar als je het iedere dag doen is het slecht voor je humeur en dus suboptimaal.

[brose]

Bedankt voor jullie hulp zover. Ik kom er met de INDEX functie nog niet helemaal uit; momenteel is dit de data:



Ik heb nu =INDEX(B2:E2, A2, -2003 + B2, false), maar dit geeft een fout.

Kan een van jullie een voorbeeld geven hoe met INDEX dit om te zetten naar bovenstaand voorbeeld?

[ Voor 12% gewijzigd door brose op 10-03-2009 13:39 ]

[Verwijderd]

Maar is dat nu de input data of al bewerkt? Voor de output moet je toch eerst id en jaar onder elkaar zetten (daar gaat die index niet over)?

[Paulusbrand]

Kan je niet een draaitabel maken?

bedragen als waarden en jaar en id als benaming?

[brose]

De input in het schermvoorbeeld is de brondata (zo heb ik het nu).

Het moet voor de import per jaar een regel worden.

Zoals in dit voorbeeld:

[brose]

Anyone?

[Bolukan]

Zoiets:

A12 tm A15 = 2
B12 = 2; B13=3; B14=4; B15=5
A16 en verder = A12+1
B16 en verder = B12
C12 en verder = INDEX($1:$65535;$A12;1)
D12 en verder = INDEX($1:$65535;1;$B12)
E12 en verder = INDEX($1:$65535;$A12;$B12)
(en verder = Kopieren naar beneden)

[brose]

Uiteindelijk heb ik het met hulp van iemand met de volgende macro voor elkaar gekregen!

Visual Basic:

' Macro (Transponeren met herhaald uniek gegeven)

Sub transponeren()

Dim r As Range

For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))

With Range("A" & Rows.Count).End(xlUp).Offset(1)

.Resize(4).Value = r.Value

.Resize(4).Offset(, 1).Value = WorksheetFunction.Transpose(Array(2008, 2007, 2006, 2005))

r.Offset(, 1).Resize(, 4).Copy
.Offset(, 2).PasteSpecial Paste:=xlValues, Transpose:=True

End With

Next

Application.CutCopyMode = False

End Sub

[ Voor 1% gewijzigd door een moderator op 12-03-2009 11:40 . Reden: code tags geplaatst ]