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]

Try

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

M.

 

[mcrossler]

Hmm... It works great for the above data but when I apply it to my actual data, I can't get it to work.

In some cases, I will have up to 10 rows and 20 columns of data but in this particular instance, only 22 of those cells have anything in them and just 5 are unique.

When I run this formula, all I get is 10/14 in the first column and nothing in the remaining 19 to it's right (it should be 10/14, 2/3, 3/4, 4/6, 5/8, , , , , , , , , , , , , , , ):

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

10/14

2

4/6

5/8

3

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

9

10

<tbody>
</tbody>

Because I'm trying to extract this data to become fields in a drop down list on a website, all the above data is set as text, not as numbers (fractions) or as a date.

Thanks!

 

[mcrossler]

Hmm... This works great for the sample data I provided in my question. However, I can't get it to work in my actual spreadsheet.

I have 10 rows and 20 columns that may (or may not) contain text data. Here's an example:

<tbody><tr class="px"wysiwyg_cms_table_grid_tr" wysiwyg_dashes_tr"="">[TD="class: grid"][/TD]
[TD="class: grid"]A
[/TD]
[TD="class: grid"]B
[/TD]
[TD="class: grid"]C
[/TD]
[TD="class: grid"]D
[/TD]
[TD="class: grid"]E
[/TD]
[TD="class: grid"]F
[/TD]
[TD="class: grid"]G
[/TD]
[TD="class: grid"]H
[/TD]
[TD="class: grid"]I
[/TD]
[TD="class: grid"]J
[/TD]
[TD="class: grid"]K
[/TD]
[TD="class: grid"]L
[/TD]
[TD="class: grid"]M
[/TD]
[TD="class: grid"]N
[/TD]
[TD="class: grid"]O
[/TD]
[TD="class: grid"]P
[/TD]
[TD="class: grid"]Q
[/TD]
[TD="class: grid"]R
[/TD]
[TD="class: grid"]S
[/TD]
[TD="class: grid"]T
[/TD]
[TD="class: grid"]U
[/TD]
[TD="class: grid"]V
[/TD]
[TD="class: grid"]W
[/TD]
[TD="class: grid"]X
[/TD]
[TD="class: grid"]Y
[/TD]
[TD="class: grid"]Z
[/TD]
[TD="class: grid"]AA
[/TD]
[TD="class: grid"]AB
[/TD]
[TD="class: grid"]AC
[/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]1
[/TD]
[TD="class: grid"]10/14
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]2
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"]5/8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]3
[/TD]
[TD="class: grid"]2/3
[/TD]
[TD="class: grid"]3/4
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"]5/8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]4
[/TD]
[TD="class: grid"]2/3
[/TD]
[TD="class: grid"]3/4
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"]5/8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]5
[/TD]
[TD="class: grid"]2/3
[/TD]
[TD="class: grid"]3/4
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"]5/8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]6
[/TD]
[TD="class: grid"]2/3
[/TD]
[TD="class: grid"]3/4
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"]5/8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]7
[/TD]
[TD="class: grid"]2/3
[/TD]
[TD="class: grid"]3/4
[/TD]
[TD="class: grid"]4/6
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr class="wysiwyg_cms_table_grid_tr wysiwyg_dashes_tr">[TD="class: grid"]8
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr>[TD="class: grid"]9
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr><tr>[TD="class: grid"]10
[/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
[TD="class: grid"][/TD]
</tr></tbody>

I can get the formula to recognize the first cell (10/14) but then it just places blanks in the remaining cells to the right. It's not seeing the 2/3, 3/4, 4/6 or 5/8.

These cells are formatted as text, they shouldn't be treated as numbers (fractions) or dates.

Thanks,
Mark

 

[Marcelo Branco]

Select A1:D7 and run this macro

Sub aTest()
    Dim dic As Object, rCell As Range
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.comparemode = vbTextCompare
    
    For Each rCell In Selection
        If rCell <> "" Then dic(rCell.Value) = Empty
    Next rCell
    
    With Range("G3").Resize(, dic.Count)
        .NumberFormat = "@"
        .Value = dic.keys
        .Sort key1:=.Cells(1), Orientation:=xlSortRows, Header:=xlNo
    End With
End Sub

M.

 

[mcrossler]

I'd still prefer not to use a macro. I'm a Excel user since the early 90s and aughts, when macros contained viruses, and don't want users of this spreadsheet to freak out when Excel tries to open a macro.

Thanks!

 

[Marcelo Branco]

With the values in A1:D7 as Text don't know how to achieve what you want using formulas.
Still thinking but not sure if it's possible.

M.

 

[Marcelo Branco]

With the values in A1:D7 as Text don't know how to achieve what you want using formulas.
Still thinking but not sure if it's possible.

M.

Tried some formulas without success. Maybe someone else...

M.

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G
1	10/14
2	4/6	5/8
3	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
7	2/3	3/4	4/6
8
9
10
11
12
13		10/14	4/6	5/8	2/3	3/4

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B13 {=IFERROR(INDIRECT(TEXT(SMALL(IF($A$1:$T$10<>"",IF(COUNTIF($A$13:A13,$A$1:$T$10)=0,ROW($A$1:$T$10)*100+COLUMN($A$1:$T$10))),1),"R00C00"),FALSE),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Haven't managed the sorting though.

 

[mcrossler]

Maybe:

	A	B	C	D	E	F	G
1	10/14
2	4/6	5/8
3	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
7	2/3	3/4	4/6
8
9
10
11
12
13		10/14	4/6	5/8	2/3	3/4

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B13 {=IFERROR(INDIRECT(TEXT(SMALL(IF($A$1:$T$10<>"",IF(COUNTIF($A$13:A13,$A$1:$T$10)=0,ROW($A$1:$T$10)*100+COLUMN($A$1:$T$10))),1),"R00C00"),FALSE),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Haven't managed the sorting though.

Perfect for now! Sorting was an added bonus.

I just needed the unique values in a line so I could TEXTJOIN them together.

Thanks!!!