Combine lists with similar headers

crossdog90

New Member
Joined
Jul 1, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with hundreds of lists that each have a header and vary in length. The headers all follow a similar format (ABCD 123AB) and I want all columns for which the headers share the first 8 characters (including the space) to be combined into a single column with the header being the first 8 characters of the original headers only. Below is a very simplified version of what I am starting with...

AAAA 100AAAAA 100BAAAA 100CAAAA 101AAAAA 201AAAAA 201BBBBB 100AABBBB 100ABBBBB 101
1​
6​
13​
22​
5​
17​
1​
16​
2​
2​
7​
14​
23​
7​
20​
2​
17​
3​
3​
8​
15​
24​
8​
21​
4​
20​
4​
4​
9​
16​
25​
15​
24​
9​
22​
5​
5​
10​
17​
25​
10​
8​
11​
18​
11​
10​
12​
13​

And here is what I would like to end up with:
AAAA 100AAAA 101AAAAA 201BBBB 100AABBBB 101
1​
22​
5​
1​
2​
2​
23​
7​
2​
3​
3​
24​
8​
4​
4​
4​
25​
15​
9​
5​
5​
17​
10​
8​
6​
20​
11​
10​
7​
21​
16​
13​
8​
24​
17​
9​
25​
20​
10​
22​
11​
12​
13​
14​
15​
16​
17​
18​

I have seen similar problems but all slightly different and I can't figure out how to apply the solutions to my problem. If I need to start off by replacing all column headers using LEFT(A1,8) so lists with the same headers can be combined I can do that...

Any help would be very greatly appreciated!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
with Power Query on cito:
AAAA 100AAAAA 100BAAAA 100CAAAA 101AAAAA 201AAAAA 201BBBBB 100AABBBB 100ABBBBB 101AAAA 100AAAA 101AAAA 201BBBB 100BBBB 101
1613225171162122512
2714237202173223723
3815248214204324844
491625152492254251595
5101725108517108
111811106201110
12137211613
82417
92520
1022
11
12
13
14
15
16
17
18

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    First8 = Table.AddColumn(Unpivot, "First Characters", each Text.Start([Attribute], 8), type text),
    RC = Table.RemoveColumns(First8,{"Attribute"}),
    Group = Table.Group(RC, {"First Characters"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each List.Sort([Count][Value], Order.Ascending)),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3", "List.4", "List.5", "List.6", "List.7", "List.8", "List.9", "List.10", "List.11", "List.12", "List.13", "List.14", "List.15", "List.16", "List.17", "List.18"}),
    RC2 = Table.RemoveColumns(Split,{"Count"}),
    Trans = Table.Transpose(RC2),
    Promo = Table.PromoteHeaders(Trans, [PromoteAllScalars=true]),
    Type = Table.TransformColumnTypes(Promo,{{"AAAA 100", Int64.Type}, {"AAAA 101", Int64.Type}, {"AAAA 201", Int64.Type}, {"BBBB 100", Int64.Type}, {"BBBB 101", Int64.Type}})
in
    Type
M-code is not optimised without coffee :biggrin:
btw. this is NOT vba
 

crossdog90

New Member
Joined
Jul 1, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thank you for your response sandy666!
Sorry for not being clear about this but the example I posted above was much smaller than the actual spreadsheet which has hundreds of columns with hundreds of headers. In the future, new lists will need to be processed too with new headings, therefore it is not feasible to type all of the headings into the code/ formula/ solution. I think the solution needs to search out matching headers and combine those lists, is there a relatively simple solution for this?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
sure,
you can try this

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    First8 = Table.AddColumn(Unpivot, "First Characters", each Text.Start([Attribute], 8), type text),
    Group = Table.Group(First8, {"First Characters"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each List.Sort([Count][Value], Order.Ascending)),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "List", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    RC = Table.RemoveColumns(Split,{"Count"}),
    Trans = Table.Transpose(RC),
    Promo = Table.PromoteHeaders(Trans, [PromoteAllScalars=true])
in
    Promo
AAAA 100AAAA 101AAAA 201BBBB 100BBBB 101
122512
223723
324844
4251595
517108
6201110
7211613
82417
92520
1022
11
12
13
14
15
16
17
18

as you can see result is the same as previous and you can use bigger table (ExcelTable) by pasting data source or whatever. Just try on example then update source table , refresh result table and see what will happen.
Hope the rule of first 8 characters is mandatory
 
Last edited:

crossdog90

New Member
Joined
Jul 1, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you again sandy666! That works almost perfectly!

There is just one issue which is that some of the final lists will be longer than 101 rows but using that power query code seems to cut off the final data after 101 rows, so I am losing some data. This probably has a simple solution but I have very limited knowledge of power query and can't see the fix
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

try this
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    First8 = Table.AddColumn(Unpivot, "First Characters", each Text.Start([Attribute], 8), type text),
    Group = Table.Group(First8, {"First Characters"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each List.Sort([Count][Value], Order.Ascending)),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extract, "SCount", each List.Count(Text.Split([List],","))),
    MaxCount = List.Max(SplitCount[SCount]),
    Split = Table.SplitColumn(Extract, "List", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv), MaxCount),
    RC = Table.RemoveColumns(Split,{"Count"}),
    Trans = Table.Transpose(RC),
    Promo = Table.PromoteHeaders(Trans, [PromoteAllScalars=true])
in
    Promo
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome
if solution from post#7 works well you can hit Like (y) in this post
have a nice day :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,522
Messages
5,636,816
Members
416,942
Latest member
kitkat22

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
Top