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:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Bob

Alice

David

2

Mark

Alice

Nancy

3

George

Sandy

Karen

Alice

Bob

David

George

Karen

Mark

Nancy

Sandy

4

Karen

Nancy

Bob

5

<tbody>
</tbody>

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 (???)

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N ​

1​

10/14​

0​

1​

2​

4/6​

5/8​

0​

0​

17​

22​

3​

2/3​

3/4​

4/6​

5/8​

0​

0​

0​

0​

6​

11​

17​

22 ​

4 ​

2/3​

3/4​

4/6​

5/8​

0​

0​

0​

0​

6​

11​

17​

22​

5 ​

2/3​

3/4​

4/6​

5/8​

0​

0​

0​

0​

6​

11​

17​

22​

6​

2/3​

3/4​

4/6​

5/8​

0​

0​

0​

0​

6​

11​

17​

22 ​

7​

2/3​

3/4​

4/6​

0​

0​

0​

6​

11​

17​

8​

<tbody>
</tbody>

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.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Bob​

2​

Mark​

Alice​

3​

George​

Sandy​

Mark​

Alice​

Bob​

George​

Mark​

Sandy​

4​

George​

Sandy​

Mark​

Alice​

5​

George​

Sandy​

Mark​

Alice​

6​

George​

Sandy​

Mark​

Alice​

7​

George​

Sandy​

Mark​

<tbody>
</tbody>

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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

10/14​

2​

4/6​

5/8​

3​

2/3​

3/4​

4/6​

5/8​

10/14​

2/3​

3/4​

4/6​

5/8​

4​

2/3​

3/4​

4/6​

5/8​

5​

2/3​

3/4​

4/6​

5/8​

6​

2/3​

3/4​

4/6​

5/8​

7​

2/3​

3/4​

4/6​

8​

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.