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).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]ID1 Description[/TD]
[TD]ID2 Amount[/TD]
[TD]ID2 Description[/TD]
[TD]ID2 Amount[/TD]
[TD]ID3 Description[/TD]
[TD]ID3 Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fuel[/TD]
[TD]1.8[/TD]
[TD]Discount[/TD]
[TD]32.12[/TD]
[TD]Tax[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Discount[/TD]
[TD]15[/TD]
[TD]Tax[/TD]
[TD]12[/TD]
[TD]Fuel[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Discount[/TD]
[TD]8[/TD]
[TD]Fuel[/TD]
[TD]44[/TD]
[TD]Tax[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tax[/TD]
[TD]87[/TD]
[TD]Discount[/TD]
[TD]24[/TD]
[TD]Fuel[/TD]
[TD]0.15[/TD]
[/TR]
</tbody>[/TABLE]


The above table should be "transposed" as below:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Fuel[/TD]
[TD]Discount[/TD]
[TD]Tax[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.8[/TD]
[TD]32.12[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.15[/TD]
[TD]15[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]44[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.15[/TD]
[TD]24[/TD]
[TD]87[/TD]
[/TR]
</tbody>[/TABLE]

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.


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Number (C) [/TD]
[TD]ID1 Description (CT)[/TD]
[TD]ID2 Amount (CU)[/TD]
[TD]ID2 Description (CV)
[/TD]
[TD]ID2 Amount (CW)[/TD]
[TD]ID3 Description[/TD]
[TD]ID3 Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fuel[/TD]
[TD]1.8[/TD]
[TD]Discount[/TD]
[TD]32.12[/TD]
[TD]Tax[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Discount[/TD]
[TD]15[/TD]
[TD]Tax[/TD]
[TD]12[/TD]
[TD]Fuel[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Discount[/TD]
[TD]8[/TD]
[TD]Fuel[/TD]
[TD]44[/TD]
[TD]Tax[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tax[/TD]
[TD]87[/TD]
[TD]Discount[/TD]
[TD]24[/TD]
[TD]Fuel[/TD]
[TD]0.15

[/TD]
[/TR]
</tbody>[/TABLE]


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".