Sorting out and counting text strings in a table

Occasional Contributor

I have attached a table extracted from a power query.  I want to turn it (B2:H21) into a first column listing down unique strings in alphabetical order (ignoring nul cells) and a second column showing the count any strings that are replicates.  Any simple and elegant trick from Excel experts here?

2 Replies
best response confirmed by YYHoe (Occasional Contributor)

@YYHoe That would involve unpivotting the data, clean-up a bit and then group (and count) and sort. An example is included in the attached file. I couldn't continue with your query as it connect to an source that was not in the workbook. But I created a new (stand-alone) table from the one in Sheet1 and connected PQ to that one. The end result is in sheet PQ.

Thank you very much, Riny. Spot on!!!