Create one list of names combining three lists

[Ian Tobin]

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

[#2]

Forgot the file

[#3]

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

[#4]

Sergei I saw your script in the post above and was wondering if you could help derive a script that has been perplexing me for days now. I am trying to create teams for an upcoming function in my school district and need to set teams by two criteria a random number that has been assigned as a student ID and then make sure that a team doesn't have more than two team members from the same school. I need a script because the day of the event we will have students missing due to absence and will need to quickly rearrange the teams. I have excel 2010 and can access 2013. 

[#5]

=IF

(

H4="Bernal ms",Neff!C6,

IF(H4="Neff MS",Connally!C6,

IF(H4="Connally Ms",Jefferson!C6,

IF(H4="Jefferson Ms",Jordan!C6,0)

)))

The function above allows me to pull students from lists in the tabs across the bottom of the spreadsheet ensuring the function changes schools each time, but the problems is making sure I pull form the top of the directed lists each time the formula returns a hit for a school. I am not familiar enough with how to write a script using the "index feature. Making the index function pull from a list until the list is exhausted. 

[#6]

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.