Create one list of names combining three lists

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?