Rows to Columns (previous thread corrupted?)

[tonkerthomas]

Good morning everybody,

It seems as though my previous attempt didn't post properly somehow (when I click on it there's nothing there at all), so let's try again:

I have a table with two key fields and one data field. I need to reorganise it so that the key fields are summarised, with the data in columns against those fields, rather than in rows. It currently looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Row[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]1001123[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]1001345[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]1001234[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]1001678[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]1001567[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]1001456[/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]1001012[/TD]
[/TR]
</tbody>[/TABLE]

... and it needs to look like this:

[TABLE="width: 800"]
<tbody>[TR]
[TD]Contract[/TD]
[TD]Row[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[TD]Charge ID[/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]1[/TD]
[TD]1001123[/TD]
[TD]1001345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10914[/TD]
[TD]2[/TD]
[TD]1001234[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]1[/TD]
[TD]1001678[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10915[/TD]
[TD]2[/TD]
[TD]1001567[/TD]
[TD]1001456[/TD]
[TD]1001012[/TD]
[/TR]
</tbody>[/TABLE]

... with the Charge IDs summarised by Contract and Row BUT appearing in the order in which they were originally listed. Any given contract can have between 2 and 10 Rows, and any given Row can have up to 20 Charge IDs.

I've tried this in a pivot table but it seems as though you have to aggregate your data somehow, which is not what I want. I have also had a bodge about in Power Query but I don't really know enough about it to make it work. I'm sure there's a simple solution to this but I can't get there by myself. Any help would be hugely appreciated.

Cheers

Jeff

 

[MickG]

Try this:-
Data on Sheet1, Results on Sheet2.

[COLOR="Navy"]Sub[/COLOR] MG05May06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant

[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
ReDim ray(1 To Rng.Count, 1 To 3)
ray(1, 1) = "Item Code": ray(1, 2) = "Row": ray(1, 3) = "Charge ID"
n = 1

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Txt = Dn.Value & Dn.Offset(, 1).Value
    [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        ray(n, 1) = Dn.Value: ray(n, 2) = Dn.Offset(, 1).Value
        ray(n, 3) = Dn.Offset(, 2).Value
        .Add Txt, Array(n, 3)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Txt)
            oMax = Application.Max(oMax, Q(1))
            Q(1) = Q(1) + 1
            [COLOR="Navy"]If[/COLOR] UBound(ray, 2) < Q(1) [COLOR="Navy"]Then[/COLOR] ReDim Preserve ray(1 To Rng.Count, 1 To Q(1))
            ray(Q(0), Q(1)) = Dn.Offset(, 2).Value
            ray(1, Q(1)) = "Charge ID"
        .Item(Txt) = Q
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
C = .Count
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(C + 1, UBound(ray, 2))
     .Value = ray
     .Columns.AutoFit
     .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sandy666]

with PowerQuery (simple way)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Contract[/td][td=bgcolor:#5B9BD5]Row[/td][td=bgcolor:#5B9BD5]Charge ID[/td][td][/td][td=bgcolor:#70AD47]Contract[/td][td=bgcolor:#70AD47]Row[/td][td=bgcolor:#70AD47]Charge ID.1[/td][td=bgcolor:#70AD47]Charge ID.2[/td][td=bgcolor:#70AD47]Charge ID.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10914​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]

1001123​

[/td][td][/td][td=bgcolor:#E2EFDA]

10914​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

1001123​

[/td][td=bgcolor:#E2EFDA]

1001345​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10914​

[/td][td]

1​

[/td][td]

1001345​

[/td][td][/td][td]

10914​

[/td][td]

2​

[/td][td]

1001234​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10914​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

1001234​

[/td][td][/td][td=bgcolor:#E2EFDA]

10915​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

1001678​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10915​

[/td][td]

1​

[/td][td]

1001678​

[/td][td][/td][td]

10915​

[/td][td]

2​

[/td][td]

1001567​

[/td][td]

1001456​

[/td][td]

1001012​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10915​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

1001567​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10915​

[/td][td]

2​

[/td][td]

1001456​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10915​

[/td][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]

1001012​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Contract", "Row"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Charge ID", each List.Distinct(Table.Column([Count],"Charge ID"))),
    Extract = Table.TransformColumns(List, {"Charge ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Charge ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Charge ID.1", "Charge ID.2", "Charge ID.3"}),
    Type = Table.TransformColumnTypes(Split,{{"Charge ID.1", Int64.Type}, {"Charge ID.2", Int64.Type}, {"Charge ID.3", Int64.Type}})
in
    Type[/SIZE]

 

[tonkerthomas]

Perfect! A solution for every occasion. It's about time I started getting into Power Query and now seems as good a time as any.

Thank you very much, both of you - as ever, I'm blown away by the generosity of the people on this board.

Have a lovely evening

Cheers

Jeff

 

[sandy666]

You are welcome

Have a nice day

 

[MickG]

You're welcome