Sort out unique cells with formula

brianclong

Board Regular
Joined
Apr 11, 2006
Messages
168
Hello. Is there any formula (not a filter, but a formula) to find and sort each unique cell? For example, I have a column of last names and they all repeat as below:

Jones
Jones
Jones
Jones
Smith
Smith
Smith
Jones
Smith
Black
Black
Black

So I would write a formula and in the formula column it would say:

Black
Jones
Smith

The list changes each week.
Is this possible? I tried using the Large formula but of course that didn't work...
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hmmm. This is giving me the #NAME error. Is UniqueRecords a formula? It doens't seem to register when I type it in alone...

Oh, hold on, I just read if I have that Add in. I don't have that. Is there any way to do this without the addin?

If I get the addin and I generate this report, do all of the people have to have that addin to see the report? Thanks.

Bah, I can't get this addin working. :( I followed the instructions but on their website it says I have to enter some VB code but they don't say which...

Is there any other way to do this with original Excel formulas?
 
Upvote 0
Thanks I'll try it again.

In the meantime, does anyone have any original Excel formula(s) to do this?
 
Upvote 0
Hi,
Book1
ABCD
1Smith3
2SmithBlack
3SmithJones
4SmithSmith
5Jones 
6Jones 
7Jones 
8Jones 
9Jones 
10Black 
11Black
12Black
Sheet2


Sort the data in descending order.

Formula in B1:

=SUMPRODUCT((A1:A12<>"")/COUNTIF(A1:A12,A1:A12&""))

In C2 and copied down,

=IF(ROWS($C$2:$C2)<=$B$1,LOOKUP(2,1/(ISNA(MATCH($A$1:$A$12,$C$1:C1,0))),$A$1:$A$12),"")

HTH
 
Upvote 0
well....u can use this nested formula !

if(countif(Range, Criteria or the cell which u wanna find with a double occurence)>1,1,0)

this will find all the cells with double occurence - will return 1 or else will return 0 !

hope it works mate...

take care...
 
Upvote 0
Thanks Guys,

Krishnakumar,

Is there any way of doing this without having to sort? the range is already sorted according to sales numbers (so the names are scattered)...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top