Duplicate cell values - Group by column

Are you using a Mac? If so, do you have Power Query?

That's OK - in that case, I don't think the version of Excel stated in your user profile is correct - please confirm which version you are using.

Have a look at the attached. If it is what you want and you have a recent version of Excel, I can explain how to do it. This is a dynamic solution - you can refresh the results table if the master table changes.

Here's the M code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A1", type text}, {"A2", type text}, {"A3", type text}, {"A4", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Column1"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Value", Order.Ascending}, {"Column1", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Column1", "Column1 - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Column1]), "Column1", "Column1 - Copy"),
    #"Removed Duplicates" = Table.Distinct(#"Pivoted Column"),
    #"Sorted Rows1" = Table.Sort(#"Removed Duplicates",{{"Value", Order.Ascending}})
in
    #"Sorted Rows1"

OK - here's what you need to do:

1. In the original workbook (not my copy) select the whole of your source table (A1 to D104).
2. With this range selected, go to the Data ribbon and click on From Table/Range in the Get & Transform area of the ribbon (on the left). This will load the table into PowerQuery.
3. In the PQ editor that is now open, click on Advanced Editor in the ribbon (near the left).
4. In the dialog that opens, replace all the code that appears with the code that I gave you above, then click on Done (bottom right).
5. Finally, click on Close & Load at the far left of the PQ ribbon.

All you need to do now is remember to refresh the table when changes are made to the source table (Refresh on the Data ribbon). This can be automated by setting auto-refresh in the query properties. To access the query properties, click Queries & Connections on the Data ribbon, then right-click the query in the pane that appears to the right and choose Properties.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You're welcome!

Please do come back and start a new thread on PQ if you need any guidance. It's a fantastic tool - I use it literally every day now.