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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Domenic

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

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

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,725
Members
410,702
Latest member
clizama18
Top