Forum Discussion

Ian Tobin's avatar
Ian Tobin
Copper Contributor
Jun 20, 2017

Create one list of names combining three lists

Hi

I am sure I have done this before but I can't find it now.  I have a spreadsheet with three lists.  I want to combine the lists into one.  On the sheet attached I have three columns of names A, B and C.  In D, I would like to list all the same names in one long list.

Thank you

Formulas & Functions
tips and tricks

24 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 20, 2017

    Hi Ian,

     

    Try this formula starting from D1 and copy it down

     

    =IF(
	ROW()<=COUNTA(A:A),
		INDEX(A:A,ROW()),
		IF(ROW()>COUNTA(A:B),
			IF(ROW()<= COUNTA(A:C),INDEX(C:C,ROW()-COUNTA(A:B)),"" ),
			INDEX(B:B,ROW()-COUNTA(A:A))
		)
)

    File is attached.

     

     

    • Ian Tobin's avatar
      Ian Tobin
      Copper Contributor
      Jun 21, 2017

      Thank you for the reply Sergei, however I think I have tried to be too clever.  I thought I would be able to change the formula to the exact specifications I was wanting but I dont seem to be able to do this.  The list I need to make is on a different sheet and there are gaps between the columns on the first sheet.  I have atached a workbook that looks more like what I really need. 

      The three columns that have data in on the first sheet need combining in a column on the second sheet if that is possible please?

      Sorry for not being specific enough in the origional post.

      Ian

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 21, 2017

        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.

         

Resources