Create List of Unique Values from several columns

[Matt]

I need to create a list of unique values from a range of columns. For
example, say Columns E - G are as follows:

E F G
Bob Dave Bill
Rick Steve Dave
Steve Rick Fred
Bill Joe Steve

I would end up with one column with the unique names:

Bob
Rick
Steve
Bill
Dave
Joe
Fred


If it is possible, I would like to have the Unique list in a separate sheet,
update automatically and not use a Macro (VBA). Is this possible?

 

[Herbert Seidenberg]

Excel 2007 PivotTable
No macro, no formulas.
http://www.mediafire.com/file/1ymzjzmzyej/09_09_09a.xlsx

 

[Max]

Here's an alternative, a formulas play which can deliver the automatic goods
as specified ..

Assume your source data in Sheet1, cols E to G, data in row1 down
In another sheet,
In A1: =OFFSET(Sheet1!$E$1,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3))
Copy A1 down to strip the 3-col source data in Sheet1 into a vertical col.
Copy down as far as required to cater for the max expected extent of source
data in Sheet1. If you expect source data to extend down to row 100 in
Sheet1, copy down by 3 x 100 = 300 rows to A300 to cover the extent.

Then
In B1: =IF(A1=0,"",IF(COUNTIF(A$1:A1,A1)>1,"",ROW()))
In C1: =IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Copy B1:C1 down to the same extent, ie to C300. Hide/minimize cols A and B.
Col C will return the required list of uniques which is dynamic to the source
data in Sheet1. Success? Celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik