Combine lists with similar headers

[crossdog90]

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!

 

[sandy666]

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

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
btw. this is NOT vba

 

[crossdog90]

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]

sure,
you can try this

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

 

[crossdog90]

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]

Give me a sec

 

[sandy666]

try this

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

 

[crossdog90]

Amazing, thank you so much!

 

[sandy666]

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