Identify unique names in a collumn

**Lewis1** · 07-29-2009, 10:47 PM

Hi all, quick question-

If I have a list in Collumn A such as:

1
1
2
2
3
4
5
5
6
6

Is there a simply excel function I can use so that in collumn B I return only the unique results. That is, collumn B will look like this

1
2
3
4
5
6

????

Thanks so much for your help!

Kind regards,
Lewis.

**pickslides** · 07-29-2009, 11:51 PM

This is a really good question, i usually do this in Access but would love to know how it do it in excel. Will wait patiently...

**windknife** · 07-29-2009, 11:59 PM

Try this,
B1

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter, then copied down.

You can also see attachment.
Hope this helps,
WindKnife

**Lewis1** · 07-30-2009, 12:33 AM

Perfect, that works perfectly - excellent function Wind!! Thanks so much!!

If you have any time it would be great if you could write a few lines on what the function actually does,

If not, no worries.

That is excellent!!

THANKS AGAIN!

Kind regards,
Lewis

**windknife** · 07-30-2009, 12:37 AM

Ha, I hope I can explain it, but my English is very poor.
Maybe, Guru can explain it for you ....
WindKnife

**DonkeyOte** · 07-30-2009, 02:28 AM

Is your real scenario really as straightforward as your example in post 1 - ie always numerics, always listed in Ascending order ?
(I am guessing not given title of thread!)

I ask only because Windknife's array formula is expensive performance wise (as he would concede himself) and there are perhaps a few possible alternatives pending answer to above q...

General rule of thumb in XL is to avoid repetitive calculations... ie better to calculate once in one cell and refer to that cell thereafter rather than continuously recalculating the same thing

For ex. if we took your example data and did the following:

C1: Count
C2: =SUMPRODUCT(--($A$1:$A$15<>""),1/COUNTIF($A$1:$A$15,$A$1:$A$15&""))

this gives us our unique count of values in the range, ie how many values we must return in our resulting list
this number is the same for all subsequent calculations and is relatively expensive to calculate so we should aim to avoid continuously calculating this figure over and over, ie calc once - store value - refer to cell thereafter:

B1: Unique Numbers
B2: =IF(ROWS(B$2:B2)>$C$2,"",SMALL($A$1:$A$15,1+COUNTIF($A$1:$A$15,"<="&N(B1))))
copied down to B16 (we're using possible array of 15 values which could all be unique)

If the values are not always numeric nor necessarily listed in order then another alternative might be to use:

B2: =IF(ROWS(B$2:B2)>$C$2,"",INDEX($A$1:$A$15,MATCH(1,INDEX(($A$1:$A$15<>"")*ISNA(MATCH($A$1:$A$15,$B$1:$B1,0)),0),0)))

Another non-formula alternative is to use XLs built in Advanced Filter to copy Unique Rows see http://www.contextures.com/xladvfilter01.html#FilterUR

**windknife** · 07-30-2009, 06:32 AM

DonkeyOte, Good advice. Because I always use Excel for small data, I haven't considered about performance before. Originally, I just want to learn function by solving real problems.
I think I must learn more knowledge in performance issue. Thanks again for your information.

Regards,
WindKnife

**arthurbr** · 07-30-2009, 08:22 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=406233