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]

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, , , , , , , , , , , , , , , ):

[TABLE="class: grid"]
<tbody>[TR]
[TD="align: center"][/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]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10/14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"]5/8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2/3[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"]5/8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2/3
[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"]5/8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2/3[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"]5/8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]2/3[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"]5/8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]2/3[/TD]
[TD="align: center"]3/4[/TD]
[TD="align: center"]4/6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


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:[TABLE="class: grid"]
<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>[/TABLE]

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
10/14
4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6
2/3	3/4	4/6
10/14	4/6	5/8	2/3	3/4

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Haven't managed the sorting though.

 

[mcrossler]

Maybe:

	A	B	C	D	E	F	G
10/14
4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6	5/8
2/3	3/4	4/6
2/3	3/4	4/6
10/14	4/6	5/8	2/3	3/4

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B13[/TH]
[TD="align: left"]{=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),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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!!!