# Sort out unique cells with formula

#### brianclong

##### Board Regular
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Krishnakumar

##### Well-known Member

In B1 and copied down,

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

HTH

#### brianclong

##### Board Regular
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
Thanks I'll try it again.

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

#### Krishnakumar

##### Well-known Member
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
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
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)...

Replies
15
Views
314
Replies
5
Views
248
Replies
5
Views
159
Replies
3
Views
304
Replies
3
Views
341

1,171,203
Messages
5,874,336
Members
433,044
Latest member
drewbizzy

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

### Which adblocker are you using?

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

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