Rows to Columns (previous thread corrupted?)

tonkerthomas

Board Regular
Joined
Feb 12, 2014
Messages
56
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:

ContractRowCharge ID
1091411001123
1091411001345
1091421001234
1091511001678
1091521001567
1091521001456
1091521001012

<tbody>
</tbody>

... and it needs to look like this:

ContractRowCharge IDCharge IDCharge ID
10914110011231001345
1091421001234
1091511001678
109152100156710014561001012

<tbody>
</tbody>

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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try this:-
Data on Sheet1, Results on Sheet2.
Code:
[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
 
Upvote 0
with PowerQuery (simple way)

ContractRowCharge IDContractRowCharge ID.1Charge ID.2Charge ID.3
10914​
1​
1001123​
10914​
1​
1001123​
1001345​
10914​
1​
1001345​
10914​
2​
1001234​
10914​
2​
1001234​
10915​
1​
1001678​
10915​
1​
1001678​
10915​
2​
1001567​
1001456​
1001012​
10915​
2​
1001567​
10915​
2​
1001456​
10915​
2​
1001012​

Code:
[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]
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top