# Combine lists with similar headers

#### crossdog90

##### New Member
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 100A AAAA 100B AAAA 100C AAAA 101A AAAA 201A AAAA 201B BBBB 100AA BBBB 100AB BBBB 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 100 AAAA 101A AAAA 201 BBBB 100AA BBBB 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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### sandy666

##### Banned - Rules violations
with Power Query on cito:
 AAAA 100A AAAA 100B AAAA 100C AAAA 101A AAAA 201A AAAA 201B BBBB 100AA BBBB 100AB BBBB 101 AAAA 100 AAAA 101 AAAA 201 BBBB 100 BBBB 101 1 6 13 22 5 17 1 16 2 1 22 5 1 2 2 7 14 23 7 20 2 17 3 2 23 7 2 3 3 8 15 24 8 21 4 20 4 3 24 8 4 4 4 9 16 25 15 24 9 22 5 4 25 15 9 5 5 10 17 25 10 8 5 17 10 8 11 18 11 10 6 20 11 10 12 13 7 21 16 13 8 24 17 9 25 20 10 22 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),
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
btw. this is NOT vba

#### crossdog90

##### New Member
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
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),
in
Promo``````
 AAAA 100 AAAA 101 AAAA 201 BBBB 100 BBBB 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

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

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

Give me a sec

#### sandy666

##### Banned - Rules violations

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),
in
Promo``````

#### crossdog90

##### New Member
Amazing, thank you so much!

#### sandy666

##### Banned - Rules violations
You are welcome
if solution from post#7 works well you can hit Like in this post
have a nice day

Replies
19
Views
303
Replies
1
Views
171
Replies
8
Views
190
Replies
12
Views
207
Replies
2
Views
99

1,130,361
Messages
5,641,660
Members
417,229
Latest member
BODYCOTE

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

### Which adblocker are you using?

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

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