Forum Discussion
Create one list of names combining three lists
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.
First,
=IF(ROW()<=COUNTA(Sheet1!A:A),
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
IF(ROW()>COUNTA(Sheet1!A:D)-(4-1),
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(ROW()<= COUNTA(Sheet1!A:H)-(8-1),
If less return third list, otherwise blanks. And if previous condition is not met return values from second list.
Hi Ian,
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.
Hi Sergei
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
Ian
Hi Ian,
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
=SUMPRODUCT(--(LEN(range)>0))
if your list is the mix of texts and numbers
Hi Sergei
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
=IFERROR(INDEX(Maths!$C$18:$C$43,MATCH(0,COUNTIF($G$2:G2,Maths!$C$18:$C$43)+IF(IF(COUNTIF(Maths!$M$6:$M$17,Maths!$C$18:$C$43)>0,1,0)=1,0,1),0)),"")
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?
Thank you
Ian
Hi Sergei
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?
Thank you
Ian
Hi Ian,
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?
