Followup to Counting unique values across an array

[e119687]

I received an excellent solution to my first post related to counting unique values. I am however faced with another challenge in that I need to display a list of the unique SN's and then which problems are associated with each.

I have attached sample data and a sample output of what I am trying to create.

Any help would be greatly appreciated.

 

[vletm]

e119687
Two versions ...
a) select cell A1 and press [ Do It ] ... (slow with details)
b) select any other cell and press [ Do It ] ... (faster)
( if run more than once then gotta manually delete result rows )

 

[e119687]

Another fantastic solution! Thanks!

 

[bosco_yip]

Or...............

Try this formula solution

1] In "SN" A32, enter formula :

=IFERROR(AGGREGATE(15,6,0+SUBSTITUTE($B$2:$CQ$25,"_","")/(0+SUBSTITUTE($B$2:$CQ$25,"_","")>A31),1),"")

And,

Select A32 >> Custom cell format, enter: 0000\_000

All copied down

2] In "Problem number" B32, copied across and down :

=IFERROR(INDEX($A$2:$A$25,AGGREGATE(15,6,ROW($A$2:$A$25)-ROW($A$1)/(0+SUBSTITUTE($B$2:$CQ$25,"_","")=$A32),COLUMN(A1))),"")

Regards
Bosco

 

[GraH - Guido]

Another alternative with Power Query

1. unpivot
2. group by SN - All Rows (AsTable)
   
3. use formula Table.Column(AsTable, "Problem number") to convert to list
4. finally extract this as a text
5. get rid of redundant column
6. load to worksheet