Unique Dynamic list of numbers from multiple columns

[juriemagic]

hi good people!,

Please help me with a formula to extract a unique list of numbers from 3 columns. The columns themselves are different lengths, compared to one another, also, each of these columns is not always the same length. The max number of data in any of these columns would be 20 000 rows, although in 95% of the time, will never contain that much data. I am working with numbers, not text. Lets use columns A, B and C. Thank you all kindly.

[JohnTopley]

Try

=IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$1000, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$1000)+($A$2:$A$1000=""), 0)), INDEX($B$2:$B$1000, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$1000)+($B$2:$B$1000=""), 0))), INDEX($C$2:$C$1000, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$1000)+($C$2:$C$1000=""), 0))), "")

Enter with Ctrl+Shift+Enter

Might be easier to copy all data to single column and use "Data" ==> "Remove Duplicates"

[juriemagic]

Hi JohnTopley,

I get an excel error "The data you entered is not valid". Yes, I do agree that "remove duplicates" would be the easier way, it's just that the data in the 3 columns change regularly and it's a bit time consuming to perform the copying and so forth...especially if this task is done 5 or more times a day, therefore the "want" to have it work automatically. I would prefer to see the resulting data update live as these columns change...

Do you think you can please see why the formula is giving this error?

[JohnTopley]

I forget to say that the formula should be put in D2. - don't know if that's the problem.

It worked OK when i tested it with 100 rows of data.

If you still have an error, post a file with say 200 rows of data.

[Tony Valko]

This would be relatively easy if you can extract the uniques in sorted order.

[JohnTopley]

VBA solution:

Please Login or Register  to view this content.

With Data starting row 1of A, B , C output in column D row 2 onwards

[juriemagic]

No, I had the formula in the right place, now it works but for some reason skips some numbers. I have also tried this on a smaller scale, and it works great then but working with 20000 rows in each of the 3 columns, looks problematic. Thanx anyways for your time, I really appreciate your efforts!!. Just to mention, the columns were not really adjacent, and only starts with data from row 3, I did alter the formula to accommodate for this, but don't think that should be the cause?

[juriemagic]

Yes Tony, that would be...

[juriemagic]

I have tried this code, it works beautifully!!!..thanx a lot JohnTopley!

[JohnTopley]

You're welcome.

If your problem has been solved, could you please mark thread as such ("Thread Tools" at top of first post)

[juriemagic]

Sure, thank you..

[Tony Valko]

Yes Tony, that would be...

Something like this...

Data Range

	A	B	C	D	E	F	G
1	7		2		5		Uniques
2	7		9		8		1
3	1		5				2
4			2				5
5			5				7
6							8
7							9
8	------	------	------	------	------	------	------

Enter this formula in G2:

=MIN(A1:A3,C1:C5,E1:E2)

This array formula** entered in G3:

=IFERROR(1/(1/MIN(IF(A$1:A$3>G2,A$1:A$3),IF(C$1:C$5>G2,C$1:C$5),IF(E$1:E$2>G2,E$1:E$2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[juriemagic]

Hi Tony Valko,

Do be EXTREMELY honest with you, I really wanted to steer away from VBA for this one, but I have already implemented the code given, and all works just fine now. HOWEVER, I WILL remember this post for future times if I should get myself in a similar position. I really do appreciate all your efforts. Thank you and have a great day..

[Tony Valko]

You're welcome. Thanks for the feedback!