If you have a version of Excel which supports worksheet functions SORTBY and UNIQUE you could do it with a couple of helper cells K3 and L3.


Two solutions in the attached, one at cell G7 which puts the results in 2 rows, sorted, the other at cell G10 which puts the results in a single row of cells, as you have, sorted.

If you don't have those functions available to you a pivot table is a very easy solution. See cell F14. Choose your number in cell G14. This also sorts.

I've added a few rows of raw data for testing, tweaked a few values in the raw data too. Cell J1 shows a list of numbers for testing.

It's interesting to note that the pivot table honours the difference between '121' and '0121' whereas the formulae lump these together.

Attached Files

msofficeforums47599Excel function help01.xlsx (17.0 KB, 7 views)