- last edited on
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.
06-20-2017 08:26 AM
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.
06-21-2017 12:23 AM
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.
06-21-2017 02:12 AM
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.
06-21-2017 05:55 AM
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.
06-21-2017 02:41 PM
I definitly prefer the formula as i dont really understand what is happening with the Query. Can you tell me what the numbers in the brackets are for on the formula you sent me on the workbook with the two sheets?
06-21-2017 04:18 PM
Ian, that formula is better to consider as prototype. It assumes in in Sheet1 you have empty columns between columns with your names; combine column in Sheet2 starts from the same second row as lists in Sheet1, etc. It could be updated using, for example, OFFSET() and/or some helper cells, some time is needed.
In current formula we compare row number in resulting list (taking into account above assumptions) with number of non-empty cells in colums with list of Sheet1.
if it is less than row number with first blank cell, we take names from first list. As soon as it is more we compare with total number of non-blank cells in columns from A to D
Here we deduct number of non-blank cells in header row (first one) in above columns, i.e in A1:D1. That's 4. Minus one since we compare as "more than", could be missed with "equal or more than".
After that similar way compare row number in combined list in Sheet2 with total number of non-blank cells in all columns from A to H excluding headers cells
If less return third list, otherwise blanks. And if previous condition is not met return values from second list.
06-21-2017 04:30 PM
By the way, about Power Query - if your are using Excel more or less regular it's worth to check at least basic things. It could be quite powerful and simple, even without any coding. From this point of view Episode 2 of Excel Table Talks on community home page https://techcommunity.microsoft.com/t5/Excel/ct-p/Excel_Cat illustrates the approach quite good.
06-22-2017 08:09 AM
I updated the formula a bit to make it more flexible. If you wand generate combined list starting from cell C3 in second sheet, enter into this cell and when copy down
=IFERROR( INDEX( OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!A$2:$A$100)), ROWS($C$3:$C3) ), IFERROR( INDEX( OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!D$2:$D$100)), ROWS($C$3:$C3)-COUNTA(Sheet1!A$2:$A$100) ), IFERROR( INDEX( OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!H$2:$H$100)), ROWS($C$3:$C3)-COUNTA(Sheet1!A$2:$A$100)-COUNTA(Sheet1!D$2:$D$100) ), "" ) ) )
In ROWS() shall be starting cell of your list as above, it returns current position within combined list when copied down.
Each COUNTA() returns number of elements in your source list.
Each OFFSET() returns your source list. It is assumed there are no blank cells in the middle of the list.
Finally INDEX() return element of source list of current row is within the range of this list, otherwise error.
With IFERROR() we are switching from one source list to next one and return empty cell if no more source lists.
Of course you may add some parameters into the cell(s) within your file not to keep all refrences within the formula and use them in formula, but that's another story.
How it works is in attached file.
06-27-2017 03:06 AM
You have done everything I have asked so far, thank you for that, but I still cannot get it to work in the actual sheet.
I think my problem is that the names in the list are not manually inputted but are pulled through from a formula so no cell in the list is really blank.
The lists are made from a formula that matches two other lists and pulls out the same names. it looks like this
I then want to use the lists made from the above formulas to make the lists we have been talking about. I can make it work in a test sheet like the one we have been talking about but not where the formulas are.
I cannot attach the actual sheet as there is lots of information on there. Do you have any ideas?
06-27-2017 03:31 AM
Ignore my last message I have found a way that works. Rather than having COUNTA in the formula I have changed it COUNTIF and used "?*" as the criteria.
Thank you for all your help on this
06-27-2017 04:30 AM
Thank you for the update. Yes, your formula is fine to exclude "". Just in case, please take into account it works with texts only (but that's your case), more universal will be
if your list is the mix of texts and numbers
06-27-2017 04:52 AM
Is there a mximum amount of arrangements I can fit into this formula? Should I be able to use the formula for four columns? Or five?
06-27-2017 05:08 AM
I guess so, will think how to automate on defined number of columns. Do you have any logic in how your lists are placed, e.g. every third column or what?
06-27-2017 05:37 AM
I have tried the following formula to do four columns but Im guessing i am missing something as it doesnt work.
I actually dont need more than four so if i can get this to work ive cracked it i think.
06-27-2017 05:56 AM
I have attaced an example on sheet 1. I have taken the = out of the beginning as I couldnt do it otherwise.
06-27-2017 05:57 AM
For such complex formulas better to use formatting - there were few misprints in your variant. This one shall work
=IFERROR( INDEX( OFFSET($B$3,0,0,COUNTA(B$3:$B$100)), ROWS($L$2:$L2) ), IFERROR( INDEX( OFFSET($D$3,0,0,COUNTA(D$3:$D$100)), ROWS($L$2:$L2)-COUNTA(B$3:$B$100) ), IFERROR( INDEX( OFFSET($G$3,0,0,COUNTA(G$3:$G$100)), ROWS($L$2:$L2)-COUNTA(B$3:$B$100)-COUNTA(D$3:$D$100) ), IFERROR( INDEX( OFFSET($J$3,0,0,COUNTA(J$3:$J$100)), ROWS($L$2:$L2)-COUNTA(B$3:$B$100)-COUNTA(D$3:$D$100)-COUNTA(G$3:$G$100) ), "" ) ) ) )