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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

brianclong

Board Regular
Joined
Apr 11, 2006
Messages
168
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?
 

brianclong

Board Regular
Joined
Apr 11, 2006
Messages
168

ADVERTISEMENT

Thanks I'll try it again.

In the meantime, does anyone have any original Excel formula(s) to do this?
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

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

brianclong

Board Regular
Joined
Apr 11, 2006
Messages
168
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)...
 

Forum statistics

Threads
1,136,586
Messages
5,676,666
Members
419,639
Latest member
ShaunAldridge

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
Top