# A formula to sort, without duplicating?

#### stir-crazy

##### New Member
Hi there,

I'm trying to figure out how I can post many values in one column, and then have a formula in an adjacent column return the values to me, sorted and without duplicating values.

So, if I pasted as many as 200 random, numbers in column A, some of which are duplicates (1, 5355, 5, 5, 204, 5385, 5355, etc.), can I have column B return the values, sorted and without repeating (1, 5, 204, 5355, 5385)?

I've been playing with AND, FALSE and COUNTIF commands, but I haven't been doing anything well except to confuse myself. AAAAAAA!

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Assuming that A2:A8 contains the data, try...

B2:

=COUNT(1/FREQUENCY(A2:A8,A2:A8))

C2, copied down:

=IF(ROWS(C\$2:C2)<=\$B\$2,MIN(IF(ISNA(MATCH(A2:A\$8,C\$1:C1,0)),A2:A\$8)),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

That didn't seem to work, I don't think

I did the ctrl+shift+enter for each formula. Here's my result:

Column A____Column B_____Column C

88650699_____2___________88543914
(BLANK) _____2___________88543914
88650699_____3___________
(BLANK) _____2___________
(BLANK)_____2___________
88543914_____2___________
(BLANK)_____2___________
(BLANK) _____2___________
84890185_____2___________
88650699_____2___________88543914

Did I miss something?

Assuming that A2:A11 contains the data, enter the first formula in cell B2 only...

=COUNT(1/FREQUENCY(A2:A11,A2:A11))

This will give you a count of unique numbers. Then, enter the following formula in C2, and copy down:

=IF(ROWS(C\$2:C2)<=\$B\$2,MIN(IF(A2:A\$11<>"",IF(ISNA(MATCH(A2:A\$11,C\$1:C1,0)),A2:A\$11))),"")

...confirmed with CONTROL+SHIFT+ENTER. The formula should return the following result for Column C...

84890185
88543914
88650699
blank
blank
blank
blank
blank
blank
blank

Hope this helps!

YES! YES!!! YEEEEEEEEEEESSSS!!!

This works Great! Thank you so much!

I need to learn a lot about these array formulas.

THANK YOU!

Well, it's mostly working anyway.

I haven't quite figured out what yet, but it seems that if the values in the A column aren't incremental increase, I get an null value in column C.

For example (1, 6, 8, 5, 9) in A, returns (1, 5, 8, 9, 0) in C.

But (1, 5, 6, 8, 9) in A, returns (1, 5, 6, 8, 9).

I'm still way too much a newbie with this. I haven't yet found anything obvious. I know it overlooks something when it gives a value of "0" in column C, instead of a blank.

I'll keep on looking, but if you have any more input, it'd be much appreciated.

I've just tried the formula and it returns the following...

1
5
6
8
9

Can you post the exact formula you're using?

Input formula in cell C2 and copied down.

=INDEX(\$A\$1:\$A\$1000,MATCH(SUM(COUNTIF(\$A\$1:\$A\$1000,C\$1:C1)),COUNTIF(\$A\$1:\$A\$1000,"<"&\$A\$1:\$A\$1000)+ISTEXT(\$A\$1:\$A\$1000)*SUM(--ISNUMBER(\$A\$1:\$A\$1000))-ISBLANK(\$A\$1:\$A\$1000),0))

The formula is an-array must hold down:

Ctrl,Shift,Enter
Book1
ABCD
1
211
353555
45204
555355
65385
7204#N/A
85385#N/A
90#N/A
105355#N/A
11
12
Sheet2

You probably should go with Domenic formula method. Because its much more effecient for large amount of data.

Hope this helps.

Sorry stir-crazy, my mistake! The reference for A2 should be absolute...

=IF(ROWS(C\$2:C2)<=\$B\$2,MIN(IF(A\$2:A\$11<>"",IF(ISNA(MATCH(A\$2:A\$11,C\$1:C1,0)),A\$2:A\$11))),"")

Hope this helps!

Hi stir-crazy:

Here is another one that might be of some interest to you ...
Book1
ABCD
1
211
353555
45204
555355
62045385
75385
85355
9
Sheet3

array formula in cell B2 is ...

=UNIQUEVALUES(SMALL(A2:A8,ROW(A2:A8)-1),1)

UNIQUEVALUES function is from MoreFunc Add-in.

Replies
1
Views
104
Replies
3
Views
159
Replies
5
Views
416
Replies
6
Views
123
Replies
7
Views
222

1,217,346
Messages
6,136,043
Members
449,981
Latest member
kjd513

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