A formula to sort, without duplicating?

[stir-crazy]

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!

 

[Domenic]

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]

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]

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]

YES! YES!!! YEEEEEEEEEEESSSS!!!

This works Great! Thank you so much!



I need to learn a lot about these array formulas.

THANK YOU!

 

[stir-crazy]

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]

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]

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
	A	B	C	D
1
2	1		1
3	5355		5
4	5		204
5	5		5355
6			5385
7	204		#N/A
8	5385		#N/A
9	0		#N/A
10	5355		#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]

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]

Hi stir-crazy:

Here is another one that might be of some interest to you ...

Book1
	A	B	C	D
1
2	1	1
3	5355	5
4	5	204
5	5	5355
6	204	5385
7	5385
8	5355
9
Sheet3

array formula in cell B2 is ...

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

UNIQUEVALUES function is from MoreFunc Add-in.