Unique Values From All Columns Formula

Hello Dears,

Please help me to get Unique Values From All Columns ASAP. I need to complete VERY VERY IMP task assignment.

How can I get the result in the column Unique_Values_From_AllColumns using Excel Formula?

Below is example. same example in attached Excel.
a1 a1 a1 a1
a2 a2 a2 a2
a3 a3 a3 a3
b4 c4 d4 e4
b5 c5 d5 e5


Result should be as below:

a1
a2
a3
b4
b5
c4
c5
d4
d5
e4
e5

This is done using an Excel Collection.

http://dailydoseofexcel.com/archives...h-collections/

Originally Posted by krjoshi

Hi nandkishorskale,

Welcome to the forum!.

Please find the attached workbook as requested.

Regards,
Joshi

Thanks a lot for your wishes Joshi saheb!
Thanks for the solution.
just 2 doubts.
1. Currently results are as below as per Excel provided by you.
Unique_Values_From_AllColumns
a1
a1
a1
a1
a2
a2
a2
a2
a3
a3
a3
a3
b4
c4
d4
d4
b5
c5
d5
d5

But I wanted the result as below
a1
a2
a3
b4
b5
c4
c5
d4
d5
e4
e5

2. Please tell, How we can increase the columns and rows of the range?

Thanks a lot Tony for this wonderful solution.
This is perfectly working fine.
But problem is that it cant handle 10 columns and 1 Lakh OR 50,000 of rows
It keeps on calculating for hours.
Do we have some solution which can handle this much of Data?

Originally Posted by Tony Valko

Try this...

Data Range

	A	B	C	D	E	F	G	H	I	J	K
1	Data								-----	Count	Uniques
2	I	H	R	M	A	D	R	Z		11	I
3	H	K	A	X	G	R	V	I			H
4											R
5											M
6											A
7											D
8											Z
9											K
10											X
11											G
12											V
13

Enter this array formula** in J2. This will return the count of the unique entries in the range. This number will also be used for an error trap in the extraction formula.

=SUM(IF(A2:H3<>"",1/COUNTIF(A2:H3,A2:H3)))

Enter this array formula in K2. There must be a cell above the 1st formula cell and this cell must not contain an entry that is also in the data range.

=IF(ROWS(K$2:K2)>J$2,"",INDEX(A$2:H$3,MIN(IF(A$2:H$3<>"",IF(ISNA(MATCH(A$2:H$3,K$1:K1,0)),ROW(A$2:H$3)-ROW(A$2)+1))),MOD(MIN(IF(A$2:H$3<>"",IF(ISNA(MATCH(A$2:H$3,K$1:K1,0)),(ROW(A$2:H$3)-ROW(A$2)+1)*10^5+(COLUMN(A2:H3)-COLUMN(A2)+1)))),10^5)))

** 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.