combine 2 columns into 1 without knowing the size

[jpreeshl]

I am trying to combine 2 columns of names into one using a formula. The problem is the list of names changes so the range size changes according to who signs up every week.
The first list (list A) may have 50 names one week then 45 the next.
The second list (list B) may have 10 names one week then 15 the next.
All the names are different so I don't need to worry about duplicates.

I have a formula on each row that brings the names from another sheet according to what group they are signed up for. So, the lower rows that don't show names just have a space ("") in them.
I want it to work for up to 75 rows without having to change the formula.


So column A would show 50 names and have the "" in the remaining 25.
Column B would show 10 names and have the "" in the remaining 65.
I want column C to just show the 60 names without any blank rows in between them.

[NBVC]

If I understand correctly, you would need to add a count function and compare it to your row number and return blank if row number is greater than total count.

something lke:

=IF(ROWS($A$2:$A2)>COUNTA(YourList),"",YourCurrentFormula)

[jpreeshl]

My current formula already returns a blank in the row if a names doesn't show up.
Column A formulas {=IFERROR(INDEX(names, SMALL(IF("Y"=Alphabetical!$I$2:$I$185, ROW($E$3:$E$186)-MIN(ROW($E$3:$E$186))+1, ""), ROW(A31))),"")}

Column B formulas {=IFERROR(INDEX(names, SMALL(IF("YC"=Alphabetical!$I$2:$I$185, ROW($E$3:$E$186)-MIN(ROW($E$3:$E$186))+1, ""), ROW(E7))),"")}

[NBVC]

Then I am not sure what your aim is.. Are you trying to combine those 2 formulas into one? Can you post a workbook showing what you are trying to achieve?

[jpreeshl]

I have attached an example spreadsheet, I think.

[NBVC]

Try array formula:

=IFERROR(INDEX(Names!$K$2:$K$185, SMALL(IF(("Y"=Names!$I$2:$I$185)+("YS"=Names!$I$2: $I$185), ROW($E$3:$E$186)-MIN(ROW($E$3:$E$186))+1, ""), ROW(A1))),"")

You'll have to replace the Names! references to your actual data references.

[jpreeshl]

THAT IS IT!! I was trying to combine those 2 letter codes in the same statement but could not figure it out. I have been online for hours looking for that simple answer.
Thank you very much for helping me out!

[Pecoflyer]

Originally Posted by jpreeshl

... For that simple answer.

Did you say simple?

[jpreeshl]

I didn't mean to minimize your effort. It was that I just had to add the + part of that function. I appreciate your time and effort you took to help me out.

[NBVC]

That was a different person who posted that remark.....

[Pecoflyer]

Come on Vito, you know you entirely deserve the praise !
It will take me a week to understand your formula...