Categories data into "baskets" VBA.

[mortu90]

Hi, I have a question regarding a categorization process in excel. Let me explain what I mean with that:


The information is displayed below based on rows (important to know that the "IDs" are randomly distributed all the time).

Number	ID1 Description	ID2 Amount	ID2 Description	ID2 Amount	ID3 Description	ID3 Amount
1	Fuel	1.8	Discount	32.12	Tax	15
2	Discount	15	Tax	12	Fuel	0.15
3	Discount	8	Fuel	44	Tax	12
4	Tax	87	Discount	24	Fuel	0.15

<tbody>
</tbody>

The above table should be "transposed" as below:

Number	Fuel	Discount	Tax
1	1.8	32.12	15
2	0.15	15	12
3	44	8	12
4	0.15	24	87

<tbody>
</tbody>

It is quite difficult for me to even put this question into words, therefore any input is highly appreciated.

Thanks!
mortu.

 

[MickG]

Try this:-
Results Start "J1".

[COLOR="Navy"]Sub[/COLOR] MG26Sep16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count + 1, 1 To 4)
        Ray(1, 1) = "Number": Ray(1, 2) = "Fuel": Ray(1, 3) = "Discount": Ray(1, 4) = "Tax"
            c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    Ray(c, 1) = Dn.Value
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 6 [COLOR="Navy"]Step[/COLOR] 2
            [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Offset(, Ac).Value
                [COLOR="Navy"]Case[/COLOR] "Fuel": Ray(c, 2) = Dn.Offset(, Ac + 1).Value
                [COLOR="Navy"]Case[/COLOR] "Discount": Ray(c, 3) = Dn.Offset(, Ac + 1).Value
                [COLOR="Navy"]Case[/COLOR] "Tax": Ray(c, 4) = Dn.Offset(, Ac + 1).Value
            [COLOR="Navy"]End[/COLOR] Select
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Range("J1").Resize(c, 4)
    .Value = Ray
    .Borders.Weight = 2
    .ColumnWidth = 10
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mortu90]

It works absolutely great Mick! You are an excel wizard! I am trying to alter the code for my bigger project.

The only issue I have now is that the "Number" column is starting from C2 and the rest of the variables "Tax","Discount", etc are starting from CT2 (ID Description), CU2 (ID Amount), etc.

Number (C)	ID1 Description (CT)	ID2 Amount (CU)	ID2 Description (CV)	ID2 Amount (CW)	ID3 Description	ID3 Amount
1	Fuel	1.8	Discount	32.12	Tax	15
2	Discount	15	Tax	12	Fuel	0.15
3	Discount	8	Fuel	44	Tax	12
4	Tax	87	Discount	24	Fuel	0.15

<tbody>
</tbody>

Thanks again for your input!
mortu.

 

[MickG]

Try this for your new data layout,:-
Results in sheet2 starting "A1"

[COLOR="Navy"]Sub[/COLOR] MG27Sep16
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count + 1, 1 To 4)
        Ray(1, 1) = "Number": Ray(1, 2) = "Fuel": Ray(1, 3) = "Discount": Ray(1, 4) = "Tax"
            c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    Ray(c, 1) = Dn.Value
        For Ac = 95 To 101 Step 2 
            [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Offset(, Ac).Value
                [COLOR="Navy"]Case[/COLOR] "Fuel": Ray(c, 2) = Dn.Offset(, Ac + 1).Value
                [COLOR="Navy"]Case[/COLOR] "Discount": Ray(c, 3) = Dn.Offset(, Ac + 1).Value
                [COLOR="Navy"]Case[/COLOR] "Tax": Ray(c, 4) = Dn.Offset(, Ac + 1).Value
            [COLOR="Navy"]End[/COLOR] Select
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = Ray
    .Borders.Weight = 2
    .ColumnWidth = 10
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mortu90]

Works perfect. Thanks a lot MickG!

A last observation: I have seen that you altered the code with "For Ac = 95 To 101 Step 2" . I am trying to understand what are these numbers taken from. Initially I thought that they are based on the R1C1 reference style. I see that Ac is between 95 to 101 but CT column starts from 98. Shouldn't be For Ac = 98 To 110 Step 2 ?

For the moment I am trying with trial and error. It would be great to know this.

Regards,
mortu

 

[MickG]

You're welcome
Ref Observation:-It based on column "C", Hence:- Set Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
i.e. Ct is offset 95 columns from "C".