A formula to sort, without duplicating?

stir-crazy

New Member
Joined
Dec 28, 2005
Messages
29
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!

Thanks so much in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,728
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!
 

stir-crazy

New Member
Joined
Dec 28, 2005
Messages
29
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?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,728
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!
 

stir-crazy

New Member
Joined
Dec 28, 2005
Messages
29

ADVERTISEMENT

YES! YES!!! YEEEEEEEEEEESSSS!!!

This works Great! Thank you so much!



I need to learn a lot about these array formulas.

THANK YOU!
 

stir-crazy

New Member
Joined
Dec 28, 2005
Messages
29
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,728

ADVERTISEMENT

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?
 

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,728
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!
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

Forum statistics

Threads
1,141,913
Messages
5,709,298
Members
421,625
Latest member
renrut_5

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