Formula to Group Duplicate Cells/Data into Single Cell (Not VBA)

[HeyItsDizzy]

Hey Guys Just after some heap been rcking my brain for days!

My Table/Range has been pupulated this way;
[TABLE="width: 626"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Window Support Channel
[/TD]
[TD]1/1800, 1/1200, 3/600,
[/TD]
[/TR]
[TR]
[TD]Window Support Channel
[/TD]
[TD]4/1800, 2/1200, 4/900,
[/TD]
[/TR]
[TR]
[TD]Window Support Channel
[/TD]
[TD]1/1800, 1/1200, 3/600,
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to create another table, where I can read this Range and pupulate it this way;
[TABLE="width: 500"]
<tbody>[TR]
[TD]Window Support Channel[/TD]
[TD]1/1800, 1/1200, 3/600, 4/1800, 2/1200, 4/900, 1/1800, 1/1200, 3/600,[/TD]
[/TR]
</tbody>[/TABLE]

And since I made that, I have also noticed noticed Duplicates, therfore is it also possible to take it further and make my table read this way;
[TABLE="width: 500"]
<tbody>[TR]
[TD]Window Support Channel[/TD]
[TD]6/1800, 4/1200, 6/600, 4/900[/TD]
[/TR]
</tbody>[/TABLE]

Note: the "1800,1200,900,600" can be any length I have just used these sizes as reference


Thanks in Advance

 

[Peter_SSs]

Your thread title says not vba. My assessment is that the problem is not feasible without vba.

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Item[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Window Support Channel[/td][td=bgcolor:#DDEBF7]1/1800, 1/1200, 3/600,[/td][td][/td][td=bgcolor:#E2EFDA]Window Support Channel[/td][td=bgcolor:#E2EFDA]1/1800, 1/1200, 3/600,4/1800, 2/1200, 4/900,[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Window Support Channel[/td][td]4/1800, 2/1200, 4/900,[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Window Support Channel[/td][td=bgcolor:#DDEBF7]1/1800, 1/1200, 3/600,[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[HeyItsDizzy]

Yes that is correct

 

[sandy666]

so you can do that with Power Query aka Get&Transform and M-code (this is NOT vba):

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Item"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Value", each List.Distinct(Table.Column([Count],"Value"))),
    Extract = Table.TransformColumns(List, {"Value", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    Extract[/SIZE]

 

[sandy666]

or more flexible

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Filter = Table.SelectRows(Split, each ([Value] <> "")),
    Group = Table.Group(Filter, {"Item"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Value", each List.Distinct(Table.Column([Count],"Value"))),
    Extract = Table.TransformColumns(List, {"Value", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract[/SIZE]

 

[Peter_SSs]

My assessment is that the problem is not feasible without vba.

What I should have said is "My assessment is that the problem is not feasible with formula as requested in thread title"

 

[sandy666]

that's right but I'm not sure this is not possible with formula

 

[HeyItsDizzy]

Thanks all, for all of your feedback, perhaps VBA would be the best solution. My main issue was that;
Since this will become a template for all reports, I need the table to clean itself up automatically when our 3rd party program writes it into excel in this way. Therefore I was hoping to have a 'find and fix' option without the need of any manual action.
However, I believe I can be somewhat flexible, and use a 1 button function to run a VBA/Power Editor Code... I'm not sure what power editor is, However, I am pretty well diversed in the basics of VBA,

Could any of you be so kind and offer a VBA suggestion?

Thanks and Kind Regards, Dizzy

 

[sandy666]

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

anyway, @Peter_SSs in your hands