excel bestand omzet naar pivot-ready indeling

[arjoderoon]

vanuit een collega uit een ander land heb ik een excel bestand aangeleverd gekregen met een datadump. Helaas in een voor mij nu onbruikbare structuur. Ik wil deze omzetten naar een pivot-ready structuur maar dat is met de hand een nogal tijdrovend klusje (en ik heb in totaal zo'n 30 bestanden op die manier gekregen).

Nu is er 1 voordeel: alle waarden die ik moet gebruiken om er een pivot-ready indeling van te kunnen maken, staan in dezelfde kolom. En er is nog een 2e voordeel: gelukkig zijn er wel verschillende niveaus gebruikt die de verschillende kenmerken aanduiden: ieder ander niveau begint met 2 voorloop spaties extra:
het 1e niveau heeft 1 voorloop spatie
het 2e niveau heeft 3 voorloop spaties
het 3e niveau heeft 5 voorloop spaties etc.

Dus op basis van het aantal spaties dat voor de echte waarde staat, kan ik in principe bepalen in welke kolom het moet komen.

Maar....


Onderstaande zijn alle niveaus die in het bestand zitten:
1. markt
2. type product
3. subsegment
4. fabrikant
5. merk
6. sku

bij niveau 6 (dus de sku's) wil ik alle voorlopende niveaus in een aparte kolom hebben.
voor niveau 5 (dus merk) wil ik alle voorlopende niveau in een aparte kolom hebben etc.

je krijgt dus eigenlijk zoiets als onderstaande:

markt	type	subsegment	fabrikant	merk	sku
x
x	x
x	x	x
x	x	x	x
x	x	x	x	x
x	x	x	x	x	x
x	x	x	x	x	x
x	x	x	x	x	x
x	x	x	x	x
x	x	x	x	x	x
x	x	x	x	x	x
x	x	x	x
x	x	x	x	x
x	x	x	x	x	x
x	x	x	x	x	x

Waarbij uiteraard per fabrikant het aantal merken weer kan verschillen, waar per merk het aantal sku's weer kan verschillen etc.
Maar ik probeer nu te bedenken hoe ik een script kan maken dat dit voor mij doet, obv van het aantal voorloopspaties in de cel (en op basis van het aantal voorloop spaties moet er omhoog gezocht worden naar het hogere niveau dat minder voorloop spaties heeft, etc).

Maar alles begint met de waarden die in kolom I staan.

Hoe kan ik dit doen met een VBA script?
onderstaande zijn de excel formules voor de verschillende kolommen die ik nu heb en die kijken naar het aantal spaties:

Kolom A:
"=IF(LEFT(RC[8],1)="" "",IF(LEFT(RC[8],3)=""   "","""",RC[8]),"""")"

Kolom B:
"=IF(LEFT(RC[7],3)=""   "",IF(LEFT(RC[7],5)=""     "","""",RC[7]),"""")"

Kolom C:
"=IF(LEFT(RC[6],5)=""     "",IF(LEFT(RC[6],7)=""       "","""",RC[6]),"""")"

Kolom D:
"=IF(LEFT(RC[5],7)=""       "",IF(LEFT(RC[5],9)=""         "","""",RC[5]),"""")"

Kolom E:
"=IF(LEFT(RC[4],9)=""         "",IF(LEFT(RC[4],11)=""           "","""",RC[4]),"""")"

Kolom F:
"=IF(LEFT(RC[3],11)=""           "",IF(LEFT(RC[3],13)=""             "","""",RC[3]),"""")"

Kolom G:
"=IF(RC[-2]="""","""",RC[-2]&RC[-3])"

Kolom H:
"=IF(RC[-2]="""","""",RC[-3]&RC[-2])"

Dank!

[edit]voorbeeld bestand nu toegevoegd[/edit]

 

[snb]

Begin met een voorbeeldbestand te plaatsen met het uitgangspunt en de gewenste eindsituatie.

 

[arjoderoon]

Ik heb een voorbeeldbestand nu toegevoegd. Hierin heb ik handmatig al de waarden in kolom I naar andere kolommen weggeschreven met een ALS formule obv het aantal voorloop spaties.

Alleen bij deze lukt het mij nu niet om dit in een macro te zetten zodat het script kan herkennen tot welk niveau het terug moet op de juiste waarde op te halen.

In kolom A komt de markt (die is voor elke regel gelijk dus die is niet zo moeilijk). Dan komt het type. Die is niet voor elke regel gelijk.
Dan komt het (sub)segment, fabrikant, merk, product. Alleen te herkennen aan specifiek aantal voorloop spaties.
Dan komt in kolom G merk-fab: een samenvoeging van merk gevolgd door fabrikant en dan daarna in kolom H merk product, een samenvoeging van merk en product.

Mijn doel is om de kolommen zo gevuld te krijgen dat ik er draaitabellen aan kan hangen om de cijfers goed in beeld te brengen.
Ik zoek met name naar een manier om het script zo te krijgen dat deze kan herkennen dat door het aantal voorloop spaties er sprake is van bijv. een merk en dat het script dus naar boven moet zoeken naar de waarde met minder spaties om deze daar te plaatsen.

Het is erg cryptisch, ik weet even niet hoe ik het beter kan verwoorden.

 

[arjoderoon]

Na wat nadenken en wat proberen heb ik nu een code die werkt!

Sub hercoderen()
Dim aantalrijen As Integer
Dim sheetname As String

sheetname = Sheets(1).Name

With Sheets(1)
If cell(1, 1).Value = "Measure : SO Values Pub/EUR (Absolute)" Then
    .Rows(1).EntireRow.Delete
    Else
End If
.Columns("A:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(1, 1) = "Markt"
.Cells(1, 2) = "Type Product"
.Cells(1, 3) = "subsegment"
.Cells(1, 4) = "fab"
.Cells(1, 5).Value = "merk"
.Cells(1, 6).Value = "prod"
.Cells(1, 7).Value = "merk - fab"
.Cells(1, 8).Value = "merk - prod"
.Cells(1, 9).Value = "oud"
.Cells(1, 15).Value = "is_sku"
.Cells(1, 16).Value = "is_merk"
.Cells(1, 17).Value = "is_fab"
.Cells(1, 18).Value = "is_subsegment"
.Cells(1, 19).Value = "is_type"
.Cells(1, 20).Value = "is_markt"

aantalrijen = .Range("J1", .Range("J2").End(xlDown)).Cells.Count - 1
    
    'kolom A
    For Each cell In Range("A2:A" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[8],1)="" "",IF(LEFT(RC[8],3)=""   "",R[-1]C,RC[8]),"""")"
    Next
    
    'kolom B
    For Each cell In Range("B2:B" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[7],3)=""   "",IF(LEFT(RC[7],5)=""     "",R[-1]C,RC[7]),"""")"
    Next

    'kolom C
    For Each cell In Range("C2:C" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[6],5)=""     "",IF(LEFT(RC[6],7)=""       "",R[-1]C,RC[6]),"""")"
    Next

    'kolom D
    For Each cell In Range("D2:D" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[5],7)=""       "",IF(LEFT(RC[5],9)=""         "",R[-1]C,RC[5]),"""")"
    Next
    
    'kolom E
    For Each cell In Range("E2:E" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[4],9)=""         "",IF(LEFT(RC[4],11)=""           "",R[-1]C,RC[4]),"""")"
    Next
    
    'Kolom F
    For Each cell In Range("F2:F" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[3],11)=""           "",IF(LEFT(RC[3],13)=""             "","""",RC[3]),"""")"
    Next
    
    'Kolom G
    For Each cell In Range("G2:G" & aantalrijen)
      cell.Value = "=IF(RC[-2]="""","""",RC[-2]&RC[-3])"
    Next
    
    'kolom H
    For Each cell In Range("H2:H" & aantalrijen)
      cell.Value = "=IF(RC[-2]="""","""",RC[-3]&RC[-2])"
    Next
    
    'kolom O
    For Each cell In Range("O2:O" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-6],11)=""           "",""x"","""")"
    Next
    
    'kolom P
    For Each cell In Range("P2:P" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-7],9)=""         "",IF(LEFT(RC[-7],11)=""           "","""",""x""),"""")"
    Next
    
    'kolom Q
    For Each cell In Range("Q2:Q" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-8],7)=""       "",IF(LEFT(RC[-8],9)=""         "","""",""x""),"""")"
    Next

    'kolom R
    For Each cell In Range("R2:R" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-9],5)=""     "",IF(LEFT(RC[-9],7)=""       "","""",""x""),"""")"
    Next
    
    'kolom S
    For Each cell In Range("S2:S" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-10],3)=""   "",IF(LEFT(RC[-10],5)=""     "","""",""x""),"""")"
    Next

    'kolom T
    For Each cell In Range("T2:T" & aantalrijen)
      cell.Value = "=IF(LEFT(RC[-11],1)="" "",IF(LEFT(RC[-11],3)=""   "","""",""x""),"""")"
    Next

End With

'workbook.save

End Sub

ik probeer nog 2 aanvullende zaken voor elkaar te krijgen:
1. ik heb nu in al die cellen als formules staan. Ik zou liever hebben dat de vba code de echte waarde erin zet (zodat ik al die formules niet in de cellen heb staan)
2. in kolom I staan stoms merknamen waar tussen haakjes nog een cijfer staat. Ik wil nu het gedeelte van de haakjes en het cijfer laten verwijderen (uit die cel-waarde). Dus soms is dat Merk (1) of Merk (2) of Merk (3) etc. En ik wil graag dat gedeelte met de haakjes inclusief het cijfer uit de celwaarde gehaald wordt. het komt er in principe op neer dat dan de laatste 3 karakters van de string (of de laatste 4 ivm spatie) van de celwaarde afgehaald worden.


Die laatste 2 dingen kom ik nog niet uit. Ik heb weleens een code langs zien komen waarbij de celwaarde direct de uitkomst van de formule was, maar dat krijg ik maar niet voor elkaar.

Hoe kan ik de laatste 2 punten voor elkaar krijgen?

 

[snb]

Ik zou het zo doen:

Sub M_snb()
   sn = Columns(9).SpecialCells(2)
   sp = Array(, , , , 0)
   
   For j = 2 To UBound(sn)
      If Len(sn(j, 1)) - Len(Trim(sn(j, 1))) < 11 Then
         sp((Len(sn(j, 1)) - Len(Trim(sn(j, 1)))) \ 2) = Trim(sn(j, 1))
      Else
         c00 = c00 & vbLf & Join(sp, ";") & ";" & Trim(sn(j, 1))
      End If
   Next
   
   sq = Split(c00, vbLf)
   With Cells(40, 1).Resize(UBound(sq) + 1)
      .Value = Application.Transpose(sq)
      .TextToColumns , 1, , , 0, -1, 0, 0, 0
    End With
End Sub

 

[arjoderoon]

dank! Het vervelende voor mij is dat ik dit nu kan plakken, het ongetwijfeld zal werken, maar ik er zelf niks door geleerd heb.
Ik zou graag willen begrijpen hoe je het hebt opgebouwd.

Wat zijn de stappen die je hier nu ingebouwd hebt?

 

[snb]

Als je iedere regel analyseert kom je er vanzelf achter.
Als je wil leren moet je mij juist niet vragen het uit te leggen.