draaitabel t.b.v. adreslijst

[lw87]

Ik heb een groot aantal adressen onder elkaar in een excel tabel staan. Ik heb een voorbeeld bijgevoegd.
Er zijn twee kolommen: 'label' en 'inhoud'.
Onder 'label' wordt steeds herhaald
naam
adres
PC
plaats
Land
tel
fax
mail
url
Onder 'inhoud' staan de adresgegevens.
Ik wil er een draaitabel van maken waarin de labelgegevens horizontaal vermeld zijn en de adresgegevns in de betreffende kolommen er onder.
Zo kan ik de gegevens gebruiken voor een mailing.

HOE DOE JE DAT?
Met 'label' in het vak kolomvelden krijg ik de labels horizontaal in beeld. Zet ik 'inhoud' in vak gegevensitems, dan krijg ik daaronder alleen een regel met totalen.

Loes

 

[jen]

Ik denk niet dat dit met een draaitabel gaat werken.

Wat je kunt doen om de gegevens anders weer te geven is een selectie maken van een adres vervolgens ga je naar bijvoorbeeld een nieuw blad en klik je op bewerken, plakken speciaal, transponeren.

De gegevens die horizontaal staan worden nu verticaal weergeven en omgekeerd.

Dit is wel veel werk, maar is sneller dan alles opnieuw invoeren.

Ik neem aan dat je de gegevens uit een ander bestand hebt geimporteerd. Misschien kun je in het originele bestand iets doen om de gegevens anders weer te geven.

succes!

 

[jzeedijk]

Hoi Loes,

Leuk idee van Jen, maar ik denk niet dat het erg aanlokkelijk is.

Zet in B1:J1 de volgende getallen:
3;8;6;9;2;4;5;7;10

Met de onderstaande formule kun je nu de adresggevens inlezen en horizontaal zetten:
B6:J99: =ALS(INDEX(adressen!$B:$B;(RIJ()-RIJ($A$6))*RIJEN(adressen!$B$2:$B$11)+B$1)="";"";INDEX(adressen!$B:$B;(RIJ()-RIJ($A$6))*RIJEN(adressen!$B$2:$B$11)+B$1))

Jeroen

 

[jen]

Jeroen,

Je hebt gelijk. Het is niet de meest ideale oplossing die ik heb gegeven, maar het werkt wel simpel (in dit geval is het veel werk).

Ik ken wel heel veel trucs in Excel, maar de formules en met name de uitleg bij de formules blijf ik lastig vinden.

Kan jij een korte uitleg geven bij de formule die je hebt gebruikt.

Jen

 

[Ingrid B]

Sub transponeren()
Dim r1 As Integer, r2 As Integer
Dim i As Integer
  For i = 2 To 10
    Cells(1, i + 2) = Cells(i, 1)
  Next i
  r1 = 2: r2 = 2
  While Cells(r1, 2) <> ""
    For i = 0 To 8
      Cells(r2, 4 + i) = Cells(r1 + i, 2)
    Next i
    r1 = r1 + 10: r2 = r2 + 1
  Wend
End Sub

kopieer de macro naar een module

 

[jzeedijk]

Hoi Jen,

In rij 1 heb ik de regelnummers gezet van de adressen (was je waarschijnlijk wel opgevallen).

De formule gebruik ik om de gegevens op te halen uit het adressenbestand. Hiervoor maak ik gebruik van de functie Index(bereik;getal). Index(bereik;1) haalt de eerste waarde uit het bereik, Index(bereik;99) haalt de 99e waarde uit het bereik.

Nu is mijn bereik in dit geval adressen!B:B, dus de hele kolom B. Mijn getal is 3 voor adres en 10 voor het URL uit het adressenbestand. Deze getallen had ik in B1:J1 gezet dus daar verwijs ik naar.
INDEX(adressen!$B:$B;B$1)

Maar met deze formule kan ik alleen het eerste record uit het bereik halen. Met (RIJ()-RIJ($A$6))*RIJEN(adressen!$B$2:$B$11) zorg ik ervoor dat per rij in blad5 een record opgeschoven wordt.
Rijen(adressen!$B$2:$B$11) is de lengte van een record, van naam1 tot naam2, in dit geval 10 rijen per record. (RIJ()-RIJ($A$6)) is de vermenigvuldigingsfactor. Op Rij 6 (1e record) is RIJ()-RIJ($A$6) gelijk aan 0, op Rij 10 (5e record) is RIJ()-RIJ($A$6) gelijk aan 4.

Voor het adres van het 5e record wordt getal dus (10-6)*10+3=43. Het adres van het 5e record staat dus in adressen!B43.
INDEX(adressen!$B:$B;(RIJ()-RIJ($A$6))*RIJEN(adressen!$B$2:$B$11)+B$1)

Tenslotte wil je natuurlijk voorkomen dat er allemaal 0-waarden komen in je nieuwe bestand. Die haal je eruit door even te checken of de index-formule gelijk is aan "". Nadeel van deze methode is dat de formule ruim 2x zo lang wordt, maar het staat wel mooier.

Heb je de database omgevormd dan kun je het bereik op blad5 kopieren en met plakken speciaal - waarden kun je de formules omzetten in waarden.

Jeroen

 

[lw87]

Pracht oplossing!!

Jeroen
Met jouw oplossing ben ik helemaal geholpen.
De volgorde van de cijfers in de cellen B1-J1 snap ik niet. Die heb ik aangepast, want de gegegevens stonden in een rare volgorde.
Mijn voorbeeldbestandje met een goed werkende formule heb ik bijgevoegd.

REUZE BEDANKT
Loes

 

[jzeedijk]

Hoi Loes,

Blij dat het goed werkt. :grin:

De volgorde had ik gehaald uit je werkblad, zelf vond ik het ook al een puinhoop, maar goed ik kende de reden niet voor de volgorde.

Jeroen

 

[jen]

Jeroen,

Bedankt voor je uitleg!

Groeten,

Jen

 

[lw87]

INDEX ontrafeld

De methode van Jeroen werkt prima.
Ik was echter wel benieuwd naar hoe het werkt.
Daar ben ik nu achter.
Wil je het ook weten, download dan de bijlage met uitleg.
Loes