Extract Unique Values from a Range of cells

[mcrossler]

I've seen formulas that can extract unique values from a row or column, like
{=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")}
but I can't make this work for a range of cells, like this:

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bob[/TD]
[TD]Alice[/TD]
[TD]David[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]Alice[/TD]
[TD]Nancy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]George[/TD]
[TD]Sandy[/TD]
[TD]Karen[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD]Bob[/TD]
[TD]David[/TD]
[TD]George[/TD]
[TD]Karen[/TD]
[TD]Mark[/TD]
[TD]Nancy[/TD]
[TD]Sandy[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Karen[/TD]
[TD]Nancy[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Where the data in A1:C4 is extracted to show only unique values of G3:N3.

Ideally, I'd also like to have the results sorted, too.

I won't be supporting this spreadsheet, so I would prefer not to use VBA to do this.

Thanks!

 

[Marcelo Branco]

Perfect for now! Sorting was an added bonus.

Eric,

Sorting the values was where I encountered problems, because =IF(A1:D7<>"",COUNTIF(A1:D7,"<"&A1:D7),"") does not work with those values - all zeros (???). See gray area.

I got something with (blue area).
=IF(A1:D7<>"",COUNTIF(A1:D7,"<"&"'"&A1:D7),"")
But there are some strange results 1 in K1, 6 in K3 (???)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

10/14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

1​

[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

4/6​

[/TD]
[TD]

5/8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"]

22​

[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2/3​

[/TD]
[TD]

3/4​

[/TD]
[TD]

4/6​

[/TD]
[TD]

5/8​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

11​

[/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"]

22
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4
​

[/TD]
[TD]

2/3​

[/TD]
[TD]

3/4​

[/TD]
[TD]

4/6​

[/TD]
[TD]

5/8​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

11​

[/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"]

22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5
​

[/TD]
[TD]

2/3​

[/TD]
[TD]

3/4​

[/TD]
[TD]

4/6​

[/TD]
[TD]

5/8​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

11​

[/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"]

22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

2/3​

[/TD]
[TD]

3/4​

[/TD]
[TD]

4/6​

[/TD]
[TD]

5/8​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

11​

[/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"]

22
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

2/3​

[/TD]
[TD]

3/4​

[/TD]
[TD]

4/6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[TD="bgcolor: #B8CCE4"]

6​

[/TD]
[TD="bgcolor: #B8CCE4"]

11​

[/TD]
[TD="bgcolor: #B8CCE4"]

17​

[/TD]
[TD="bgcolor: #B8CCE4"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have to leave now. I hope you find a solution

M.

 

[Eric W]

Marcelo, I played around with several sorting formulas based on the classic COUNTIF("<") formula, and like you, couldn't quite get them to work. I keep thinking I'm just missing a piece somewhere. I'll keep playing around with it.

mcrossler, glad that works for you! If I figure out the sorting version, I'll let you know.

 

[Marcelo Branco]

Marcelo, I played around with several sorting formulas based on the classic COUNTIF("<") formula, and like you, couldn't quite get them to work. I keep thinking I'm just missing a piece somewhere. I'll keep playing around with it.

Yes, doesn't work with such fractions as text - maybe we're missing something "obscure"
With ordinary texts like names worked perfectly for me.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Mark​

[/TD]
[TD]

Alice​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

George​

[/TD]
[TD]

Sandy​

[/TD]
[TD]

Mark​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Alice​

[/TD]
[TD]

Bob​

[/TD]
[TD]

George​

[/TD]
[TD]

Mark​

[/TD]
[TD]

Sandy​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

George​

[/TD]
[TD]

Sandy​

[/TD]
[TD]

Mark​

[/TD]
[TD]

Alice​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

George​

[/TD]
[TD]

Sandy​

[/TD]
[TD]

Mark​

[/TD]
[TD]

Alice​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

George​

[/TD]
[TD]

Sandy​

[/TD]
[TD]

Mark​

[/TD]
[TD]

Alice​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

George​

[/TD]
[TD]

Sandy​

[/TD]
[TD]

Mark​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in G3 copied across
=IFERROR(INDIRECT(TEXT(SMALL(IF($A$1:$D$7<>"",IF(1+COUNTIF($A$1:$D$7,"<"&$A$1:$D$7)-SUM(COUNTIF($F3:F3,$A$1:$D$7))=1,ROW($A$1:$D$7)*10^5+COLUMN($A$1:$D$7))),1),"R0C00000"),0),"")
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

A possible solution using formulas


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

10/14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

4/6​

[/td][td]

5/8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2/3​

[/td][td]

3/4​

[/td][td]

4/6​

[/td][td]

5/8​

[/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

10/14​

[/td][td="bgcolor:#D9D9D9"]

2/3​

[/td][td="bgcolor:#D9D9D9"]

3/4​

[/td][td="bgcolor:#D9D9D9"]

4/6​

[/td][td="bgcolor:#D9D9D9"]

5/8​

[/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2/3​

[/td][td]

3/4​

[/td][td]

4/6​

[/td][td]

5/8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2/3​

[/td][td]

3/4​

[/td][td]

4/6​

[/td][td]

5/8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

2/3​

[/td][td]

3/4​

[/td][td]

4/6​

[/td][td]

5/8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

2/3​

[/td][td]

3/4​

[/td][td]

4/6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in G3 copied across until you get a blank (gray area)
=IFERROR(INDIRECT(TEXT(SMALL(IF($A$1:$D$7<>"",IF(COUNTIF($A$1:$D$7,"<"&"'"&$A$1:$D$7)-COUNTIF($A$1:$D$7,$A$1:$D$7)+1-SUM(COUNTIF($F3:F3,$A$1:$D$7))=1,ROW($A$1:$D$7)*10^5+COLUMN($A$1:$D$7))),1),"R0C00000"),0),"")
Ctrl+Shift+Enter

M.