Unigue list from items in each line of list

Sorry I didnt really know how to phrase my subject line. My data is arranged like this:

Please Login or Register  to view this content.

I need a unique list of each of those 6 digit codes. Only way I can figure it is a long way around:
1) used excels Duplicate Finder to get my unique fields (for instance, in the above lines 2 and 3 match, so DupeFinder would delete one).
2) Use TextToColumns to separate each 6-digit code
3) Put them all in a column and used Advance filter to get a list of unique values.

Is there a better way?

XOR LX,
I think I followed everything, but I'm having an issue. My range doesn't start in the same place your example did, so I had to make some changes. I get a correct count, and the first unique item shows up fine, but not the others. Can you take a look at my example and advise? Thanks!

And that was exactly it! Thanks so much. THIS time it took longer doing it the formula way, but NEXT time this is going to come in real handy. Thanks so much for showing me the way. Next step for me is to figure out how to map all the nameranges and formulas to a function so I can easily repeat this on demand; I've bookmarked the thread you've referenced so I can use it when I'm ready for my next step.

XOR LX, I'm glad you spotted that. I hadn't even noticed that I was missing a value. That would have been a problem later on. Good eye!

CAABYYC,
I tried Option 1, concatenating all of my Comptroller Object Codes into A1. I copied your array formula into H1, did a CSE. result was nothing.
I then tried Option 2, concatenating all of my Comptroller Object Codes into A1. I copied your helper formula into D1. Result was "/".
I copied the index formula to F1; results was nothing.

I had deleted my post after I saw your sample format, so please disregard the one with "option 1" and "option 2"
please do the test again by using following formula in your sample file.


X5=IF(ROW(A1)=1,N5,X4&N5)
W5= MID(CLEAN(SUBSTITUTE(SUBSTITUTE(INDEX(X:X,COUNTA(X:X)+4),",",),CHAR(10),)),(ROW(A1)-1)*6+1,6)
V5=IF(MATCH(W5,W:W,)=ROW(),ROW(),"/")
S5=IFERROR(INDEX(W:W,SMALL(V:V,ROW(A1))),"")

Originally Posted by jomili

XOR LX, I'm glad you spotted that. I hadn't even noticed that I was missing a value. That would have been a problem later on. Good eye!

CAABYYC,
I tried Option 1, concatenating all of my Comptroller Object Codes into A1. I copied your array formula into H1, did a CSE. result was nothing.
I then tried Option 2, concatenating all of my Comptroller Object Codes into A1. I copied your helper formula into D1. Result was "/".
I copied the index formula to F1; results was nothing.

CAABYYX,
I've verified that your method works as well. Great! Now I have two ways to do it. I'll have to dive into both to figure which is more easily repeatable going forward. Thanks so much for showing this method.

In case anyone is still interested in helping, I've ALMOST go this automated, just hitting a snag on the final formula:

Please Login or Register  to view this content.

I'm not sure how to get VAL to countup from 1 to my VAL value, but I know I need that at both the front and back of my formula. Any help is appreciated.

Okay, I've got a first working solution (see below). Right now it only works for a single column, and the separator has to be a comma. As I find time to work on it I'll add the option for the user to put in the separator. Also would like to figure out how to do the same for a multi-column range. Any help is appreciated. Current code below:

Please Login or Register  to view this content.

Okay, finished as far as I know to do it. To use it, select your single-column range of cells containing your separated values, run the macro, key in whatever your separator is. In the top cell next to your selected data the count of unique items in that range will appear. In the next column over the unique values appear.

Please Login or Register  to view this content.