Sort out unique cells with formula

[brianclong]

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

 

[Krishnakumar]

If you have MOREFUNCAdd-In

In B1 and copied down,

=INDEX(UNIQUEVALUES($A$1:$A$12,1),ROWS($A$1:$A1))

HTH

 

[brianclong]

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?

 

[Krishnakumar]

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

See: http://www.mrexcel.com/board2/viewtopic.php?t=197061

HTH

 

[brianclong]

Thanks I'll try it again.

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

 

[Krishnakumar]

Hi,

Book1
	A	B	C	D
1	Smith	3
2	Smith		Black
3	Smith		Jones
4	Smith		Smith
5	Jones
6	Jones
7	Jones
8	Jones
9	Jones
10	Black
11	Black
12	Black
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]

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]

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

 

[Krishnakumar]

Hi,

See: http://www.mrexcel.com/board2/viewtopic.php?&p=1003807

HTH