Create one list of names combining three lists

Hi Ian,

Here is the solution with PowerQuery, that's Excel tool which is most suitable for such tasks. If you are on Excel 2013 is shall be downloaded and installed (fast and stright forward process), in Excel 2016 it's built-in (named as Get & Transform).

I don't know if you are familiar with it, here are step by step instructions, same is in attached file with screenshots.

1) Select all columns with source lists and name them somehow
2) Ribbon->Data click on From Table/Range
3) In appeared Query Editor remove Change Types step
4) Select (with Ctrl) columns with your names, right click on any and Remove Other Columns:
5) In Ribbon click Use First Row as Header
6) Select all columns and in Ribbon->Transform click Unpivot Columns
7) Select new column "Attributes", Right click and Remove
8) Double click on "Value" column name, enter any name you want, Enter
9) In Ribbon select Close & Load To (it's disabled for the existing query, but available
for new one), after that select Table, Existing Sheet, and any place in your Sheet2

You will have in Sheet2 the table with combined list of names. If you make any changes in source data in Ribbon->Data click on Refresh All.

If you still prefer formulas let us know, will update then.

Hi Ian,

Very fast modification of the formula is

=IF(
 ROW()<=COUNTA(Sheet1!A:A),
  INDEX(Sheet1!A:A,ROW()),
  IF(ROW()>COUNTA(Sheet1!A:D)-(4-1),
   IF(ROW()<= COUNTA(Sheet1!A:H)-(8-1),INDEX(Sheet1!H:H,ROW()-COUNTA(Sheet1!A:D)+4),"" ),
   INDEX(Sheet1!D:D,ROW()-COUNTA(Sheet1!A:A)+1)
  )
)

but above is very unflexible. Another approach could be using of Power Query.

I'll modify a bit some later to make the formula more human friendly, so far the result is in attached file.