Excel - create new list

Question

In Excel, I have a list of participant ID numbers in Sheet 1, column A.

On sheet 2, in the same format, I have a list participant ID numbers for the next session.

Most of the participants are the same, but a couple will have dropped off, and a few will have been added.

On sheet 3, in column A, I wish to have a formula which looks to the lists on sheets 1 and 2, and creates a consolidated list of participant ID numbers.

I have found that if I lay the two lists side by side on sheet 3 (columns A and B), I can do this in a third column, but cannot seem to do this when the source data are on different sheets.

Does anyone know how to create the list that I am looking for?

Answer 1

You can copy and paste the list from Sheet1 into Sheet3, then copy the list in Sheet2 into Shett3, just below the last value you pasted a bit earlier.

After that select the column and go to 'Data' > 'Remove duplicates'.

This should give you the list you are looking for.

Answer 2

A formula approach:

On Sheet3, cell A1 enter

=IF(COUNTA(Sheet1!A:A)>=ROW(),INDEX(Sheet1!A:A,ROW()),IF(COUNTA(Sheet2!A:A)>=ROW()-COUNTA(Sheet1!A:A),INDEX(Sheet2!A:A,ROW()-COUNTA(Sheet1!A:A)),""))

Copy down as far as required.

In B1 enter

=IF(AND(A1<>"",COUNTIF($A$1:A1,A1)=1),ROW(),"")

Copy down.

In C1 enter

=IFERROR(INDEX(A:A,SMALL(B:B,ROW(A1))),"")

Copy down. Hide columns A and B if you want.