Finding unique items only from a non-single column (row) range

[Mike Szczesny]

I have found a cool, but crazy complicated, yet robust formula to list the items for a non single data row (column) range. Instead of just listing all items from this range, I would like to list only the unique items. I want to remove duplicate items. Is it possible to incorporate the frequency and transpose functions in this formula? Or would I need another formula to accomplish this? I cannot seem to get my mind "wrapped around this" in finding the solution for this formula. Any help would be greatly appreciated.
Mike Szczesny

Excel 2012
	A	B	C	D	E
1	5	18	66	4	3
2	8	1	10	4	58
3	88	17	5	85	12
4	33	77	78	10	99
5	55	5	44	38	39
6
7	Items listed by column, then next column, etc.
8	5
9	18
10	66
11	4
12	3
13	8
14	1
15	10
16	4
17	58
18	88
19	17
20	5
21	85
22	12
23	33
24	77
25	78
26	10
27	99
28	55
29	5
30	44
31	38
32	39
Sheet1

Cell Formulas
Range	Formula
A8	{=IFERROR(INDEX($A$1:$E$5,INT(SMALL(IF($A$1:$E$5<>"",(ROW($A$1:$E$5)-ROW($A$1)+1)*10^9+COLUMN($A$1:$E$5)+COLUMN($A$1)+1),ROWS($A$8:A8))/10^9),MOD(SMALL(IF($A$1:$E$5<>"",(ROW($A$1:$E$5)-ROW($A$1)+1)*10^9+COLUMN($A$1:$E$5)-COLUMN($A$1)+1),ROWS($A$8:A8)),10^9)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ingolf]

Try this in A8 and drag down

=IFERROR(INDEX($A$1:$E$5,MOD(ROWS(A$8:A8)-1,ROWS($A$1:$E$5))+1,INT((ROWS(A$8:A8)-1)/ROWS($A$1:$E$5))+1),"")

It is normal formula not CSE

 

[XOR LX]

@Ingolf

I think the OP wants unique returns only.

@Mike Szczesny

The solution for numerics is quite a bit more straightforward than that for non-numerics (or a mixture of the two datatypes). Can you confirm that, just as in your example, the dataset consists of only the former?

Regards

 

[Mike Szczesny]

@XOR LX

I would gladly consider all numeric, non-numeric, and numeric and text solutions

 

[Ingolf]

OK. Mike, next time even say HELLO to someone who tries to help you.
Try this: In A8

=IFERROR(INDIRECT(TEXT(MIN(IF($A$1:$E$5<>"",IF(COUNTIF(A$7:A7,$A$1:$E$5)=0,ROW($A$1:$E$5)*10^4+COLUMN($A$1:$E$5)))),"R0000C0000"),FALSE),"")

This is CSE formula. (Control+Shift+Enter)

 

[Mike Szczesny]

OK. Hi Ingolf. Thanks for your response. I will definitely study this formula. Interesting approach. I would have never thought about using the indirect function.