Extracting unique entries from table with multiple rows and columns

[Bonnister]

Hello folks

This little problem has been driving me nuts, I've tried various array formulas and other weird and wonderful ideas but cannot get the result I need! So any help is appreciated

Is there a way of extracting the unique entries from a table of data (ie: not just one column or row but multiples of), and listing the results of this extraction in a single row? I've attached an example of what I'm trying to get to - in this case a 4x5 table of 20 entries (some of which are duplicates), and all I want to do is pull out the unique entries into a single row for use elsewhere?

Is this possible just using Excel (I'd prefer not to go down the VBA route for various reasons, but if VBA is absolutely necessary I'm open to suggestions about this too!)

Thanks in advance to anyone who can help me

Kenny

[MarkinTX]

Copy all of the columns to on single column.
Then highlight that column and click remove duplicates under the data tab.
If you need them to read left to right, then copy , paste special, and click transpose will copy them to a row instead of a column


<----Please click the star if this helps you.

[Fotis1991]

Hi

See if this helps you.

[Pete_UK]

This lists them down the sheet rather than across, but it's easy enough to transpose them if really necessary. Put this array* formula in B10:

=IFERROR(INDEX($B$1:$E$5,MIN(IF(COUNTIF($B$9:B9, $B$1:$E$5)=0,ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1)),MATCH(0,COUNTIF($B$9:B9,INDEX($B$1:$E$5,MIN(IF(COUNTIF($B$9:B9,$B$1:$E$5)=0,ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1)), ,1)),0),1),"")

and copy down as far as you need to - I've copied to row 20 in the attached workbook.

* An array formula must be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to amend the formula then you will need to use CSE again, although you can copy the formula to other cells using the normal method(s).

Hope this helps.

Pete