# Unigue list from items in each line of list

1. ## 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?

2. ## Re: Unigue list from items in each line of list

Hi.

http://excelxor.com/2014/09/30/list-...rated-strings/

in which you'd make a minor change to two of the constructions I give to account for the fact that your entries are separated by commas, not spaces, viz:

Arry1 becomes:

=1+LEN(Range1)-LEN(SUBSTITUTE(Range1,",",""))

Arry3 becomes:

=INDEX(TRIM(MID(SUBSTITUTE(Range1,",",REPT(" ",999)),TRANSPOSE(999*(ROW(INDIRECT("1:"&MAX(Arry1)))-1)+1),999)),N(IF(1,1+INT((Arry2-1)/MAX(Arry1)))),N(IF(1,1+MOD(Arry2-1,MAX(Arry1)))))

If you have any difficulties adapting that solution to meet your needs then just let me know.

Regards

3. ## Re: Unigue list from items in each line of list

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!

4. ## Re: Unigue list from items in each line of list

Sure. The first thing I notice is that you haven't committed the column Q formulas as array formulas.

Regards

5. ## Re: Unigue list from items in each line of list

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.

6. ## Re: Unigue list from items in each line of list

You're welcome!

Actually I also noticed something a bit more interesting, which occurs on my machine using Excel 2010 (though perhaps it's not an issue for you?), and that is that you are missing an 8th result (in cell Q12), i.e. 7672AZ.

This is because the initial clause in the formula in that cell, i.e.:

IF(ROWS(\$1:8)>\$P\$5

is evaluating to TRUE, and so returning a blank, the reason being that, if you change the cell format for P5 to e.g. Number with 15 decimal places you'll see that it's not actually 8 at all, but 7.999999999999980!

Sometimes these floating-point arithmetic rounding errors are a real pain in Excel! Based on this, I would use, for P5:

=ROUND(SUM((Arry3<>"")/MMULT(0+(Arry3=TRANSPOSE(Arry3)),ROW(INDIRECT("1:"&COUNTA(Arry3)))^0)),0)

I may update my blogpost as well with this in mind, so thanks for (inadvertently!) bringing it to my attention.

Regards

7. ## Re: Unigue list from items in each line of list

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))),"")

8. ## Re: Unigue list from items in each line of list

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.

9. ## Re: Unigue list from items in each line of list

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.

10. ## Re: Unigue list from items in each line of list

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.

11. ## Re: Unigue list from items in each line of list

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.

12. ## Re: Unigue list from items in each line of list

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.``

13. ## Re: Unigue list from items in each line of list

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.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1