Aladin's 3 columns

[jahnosha]

Hi all

if I have three columns of text that I want listed into one column what function would I use? I'm partially using this:

http://www.mrexcel.com/archive/Formulas/28674.html

in his example, how would I make a fourth column combining all the others>?

 

[Fazza]

A non-formula, non-code approach FYI.

Say the source data is given the defined name MyTable and has headers List1, List2 & List3. Save the file. Start an external data query such as via ALT-D-D-N & follow the wizard to the end choosing the option to edit in MS Query. Via the 'SQL' button edit the SQL to

SELECT DISTINCT *
FROM (SELECT List1
FROM MyTable
UNION ALL
SELECT List2
FROM MyTable
UNION ALL
SELECT List3
FROM MyTable)
WHERE List1 Is Not Null

Then the 'open door' icon to complete the query table into a worksheet. This is refreshable like a pivot table. As it is a query and doesn't use formulas it will be fast enough on tens of thousands of records; it can pull data from the same workbook, closed workbooks or database files. HTH. regards