List unique values from multiple columns

[macrorookie]

Hello,
I've searched through old posts and just can't seem to find the right solution for my needs.
The attached sample workbook has four columns of data (A5 thru D168). Much of the data in these columns is duplicate. I need to create a new column (column E) that simply lists the unique values of my entire data set (e.g., A5 thru D168). I'd like to ignore blank cells.
Any help would be greatly appreciated.

[Richard Buttrey]

Is this a one off task or to be repeated.

If the former then the pragmatic answer is to copy all the values from A:D into the single column E and then select all the column E value and use the 'Remove Duplicates' option from the Ribbon menu.

[macrorookie]

Great question, Richard. I'll end up recording a macro to repeat the process, so your solution may indeed work.

[Tony Valko]

See if this helps...

https://www.excelforum.com/showthread.php?t=927634

[macrorookie]

Actually, I don't know how to past data from multiple columns into a single column. Embarrassing.

[macrorookie]

Hi Biff -
Thanks so much for the response. There's a chance I'm being dense; but I can't get your second formula to work for me (from the post link you sent me).
Using my original sample workbook, I created the following formula. Unfortunately, I'm getting the "#N/A" message.

{=IF(ROWS(F$6:F6)>E$6,"",INDEX(A$6:D$300,MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:D$300,F$1:F1,0)),ROW(A$6:D$300)-ROW(A$6)+1))),MOD(MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:C$300,F$1:F1,0)),(ROW(A$6:D$300)-ROW(A$6)+1)*10^5+(COLUMN(A6:D300)-COLUMN(A6)+1)))),10^5)))}

Any clues?
Thanks again.
Jeff

[macrorookie]

Hi Biff -
Thanks so much for the response. There's a chance I'm being dense; but I can't get your second formula to work for me (from the post link you sent me).
Using my original sample workbook, I created the following formula. Unfortunately, I'm getting the "#N/A" message.

{=IF(ROWS(F$6:F6)>E$6,"",INDEX(A$6:D$300,MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:D$300,F$1:F1,0)),ROW(A$6:D$300)-ROW(A$6)+1))),MOD(MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:C$300,F$1:F1,0)),(ROW(A$6:D$300)-ROW(A$6)+1)*10^5+(COLUMN(A6:D300)-COLUMN(A6)+1)))),10^5)))}

Any clues?
Thanks again.
Jeff

[Tony Valko]

{=IF(ROWS(F$6:F6)>E$6,"",INDEX(A$6:D$300,MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:D$300,F$1:F1,0)),ROW(A$6:D$300)-ROW(A$6)+1))),MOD(MIN(IF(A$6:D$300<>"",IF(ISNA(MATCH(A$6:C$300,F$1:F1,0)),(ROW(A$6:D$300)-ROW(A$6)+1)*10^5+(COLUMN(A6:D300)-COLUMN(A6)+1)))),10^5)))}

The highlighted range should be D$300.

Here's your file with this implemented.

[macrorookie]

Thank you so much, Biff. Perfect solution. Your help is greatly appreciated.

[Tony Valko]

You're welcome. Thanks for the feedback!