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;

Window Support Channel 1/1800, 1/1200, 3/600, Window Support Channel 4/1800, 2/1200, 4/900, Window Support Channel 1/1800, 1/1200, 3/600, <tbody> </tbody>

<tbody>
</tbody>

I am trying to create another table, where I can read this Range and pupulate it this way;

Window Support Channel

1/1800, 1/1200, 3/600, 4/1800, 2/1200, 4/900, 1/1800, 1/1200, 3/600,

<tbody>
</tbody>

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;

Window Support Channel

6/1800, 4/1200, 6/600, 4/900

<tbody>
</tbody>

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?

Item	Value		Item	Value
Window Support Channel	1/1800, 1/1200, 3/600,		Window Support Channel	1/1800, 1/1200, 3/600,4/1800, 2/1200, 4/900,
Window Support Channel	4/1800, 2/1200, 4/900,
Window Support Channel	1/1800, 1/1200, 3/600,

 

[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