Forum Discussion
Create one list of names combining three lists
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
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
Is it possible to include in the formula, the merged list without duplicates. Let's say that the 3 lists have one common name so in the merged list it will show once.
Ian, one more modification. If you updated on
=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)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0) ), IFERROR( INDEX( OFFSET($G$3,0,0,COUNTA(G$3:$G$100)), ROWS($L$2:$L2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0) ), IFERROR( INDEX( OFFSET($J$3,0,0,COUNTA(J$3:$J$100)), ROWS($L$2:$L2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)-IFERROR(COUNTIF(G$3:$G$100,"?*"),0) ), "" ) ) ) )i.e change COUNTA(range) on IFERROR(COUNTIF(range,"?*")), but not within OFFSET, when you may delete any column(s) except first one. After deleting formula will look like
=IFERROR( INDEX( OFFSET($B$3,0,0,COUNTA(B$3:$B$100)), ROWS($K$2:$K2) ), IFERROR( INDEX( OFFSET($D$3,0,0,COUNTA(D$3:$D$100)), ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0) ), IFERROR( INDEX( OFFSET(#REF!,0,0,COUNTA(#REF!)), ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0) ), IFERROR( INDEX( OFFSET($I$3,0,0,COUNTA(I$3:$I$100)), ROWS($K$2:$K2)-IFERROR(COUNTIF(B$3:$B$100,"?*"),0)-IFERROR(COUNTIF(D$3:$D$100,"?*"),0)-IFERROR(COUNTIF(#REF!,"?*"),0) ), "" ) ) ) )but IFERROR() in counting of rows numbers return correct result.
Please not COUNTA doesn't work here since COUNTA(#REF!) always returns 1
Forgot the file
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) ), "" ) ) ) )I have attaced an example on sheet 1. I have taken the = out of the beginning as I couldnt do it otherwise.
Yes but I keep getting 'You've entered too many arguments dfor this function'
Here you initial lists are in B, D, G, J and you put combined list into L, correct?
I have tried the following formula to do four columns but Im guessing i am missing something as it doesnt 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,CONTA(J$3:$J$100)),ROWS($L$2:$L2)COUNTA(B$3:$B$100)COUNTA(D$3:$D$100)-COUNTA(G$3:$G$100)),""))))
I actually dont need more than four so if i can get this to work ive cracked it i think.
Thanks again
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?
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
