Combine lists with similar headers

crossdog90

New Member
Joined
Jul 1, 2020
Messages
4
Office Version
365
Platform
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!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,763
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
365
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,763
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
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,763
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,763
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,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top