Reduce an excel 2d table to a single row 'list' (and remove the duplicates

Question

I have a table.

    1       2       3       4
A   red     purple  green   red
B   blue    yellow  white   brown
C   pink    green   purple  red
D   pink    pink    orange  white
E   green   red     hazel   black
F   orange  orange  blue    orange

I want to return (into a range) a list of every colour that appears (but only one entry in the list per colour, so no duplicates). I have found many answers for the single col version, but I really would like to extend to 2D. I would prefer an array formula solution than a VBA solution (though I'll give it a go). see this for example.

Ignore Duplicates and Create New List of Unique Values in Excel

The table may occupy any position on a sheet!

Gary's Student · Accepted Answer · 2014-09-14T21:18:31.327

1

To extract uniques from a two-dimensional table, see:

Coderre Formula

EDIT#1:

In this example the 4X6 table is in C4 thru F9

The helper column is H4 thru H27
The Uniques are in column I starting in I4

In H4 enter:

=OFFSET($C$4,ROUNDUP(ROWS($1:1)/4,0)-1,MOD(ROWS($1:1)-1,4))

and copy down

In I4 enter:

=H4

In I5 enter the array formula:

=IFERROR(INDEX($H$5:$H$27, MATCH(0, COUNTIF($I$4:I4, $H$5:$H$27), 0)),"")

and copy down

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Here is what we see:

enter image description here

edited Sep 14 '14 at 21:18

answered Sep 13 '14 at 00:26

Gary's Student

95,722
10
59
99

Do you have a solution for a case where the table does not occupy the top left of the sheet. I didn't mean to imply this situation with my row/col layout above! – Andrewb Sep 14 '14 at 20:40

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

Might be achieved by entering something ("z" would do) at the intercept of 1 and A, then creating a PivotTable with multiple consolidation ranges (see for example) and in the new sheet apply Advanced Filter to the Value column, Copy to another location, Copy to: where desired and select Unique records only. The PT and drill-down details can then be deleted.

edited May 23 '17 at 10:33

Community

1
1

answered Sep 13 '14 at 00:33

pnuts

58,317
11
87
139

score 0 · Answer 3 · answered Apr 28 '23 at 10:59

This has become very easy in modern Excel. You can use UNIQUE on a list, and there are a few options for getting the data all in one list. For a sample list like this:

You could use VSTACK to combine the columns and then apply UNIQUE, like so:

=UNIQUE(VSTACK(R1C1:R3C1, R1C2:R3C2, R1C3:R3C3))

Or you could use TOCOL, like so:

=UNIQUE(TOCOL(R1C1:R3C3))

Reduce an excel 2d table to a single row 'list' (and remove the duplicates

3 Answers3