Compare Data on multiple tabs

Question

I have three tabs in the same workbook that I need to find the differences. The list consists of enrolled members over a three year period which I need to figure out who dropped and enrolled. I can compare based on one column if that is easier.  Here is an example

Sheet 1= 2014

Sheet 2 =2015

Sheet 3 =2016

All heading match across all three sheets. Name  OCA ID are the three that should be consistent.

I need to see if the same OCA (numeric) is on all three sheets, if not then some message such as false would show.

Honestly I am terrible with using vlookup and pivot tables. Any help is appreciated

Answer 1

Here's the best way. Insert a new sheet, and copy the Name OCA ID columns from 2014 into  columns A:C.  Then, in D2 (assuming you have headers in row 1) enter the formula

=A2 & " " & B2 & " " & C2

and in cell E2, enter 2014. Then copy those cells down.  Copy the information from 2015, and put the cells below the list from 2014, and do the same for column D and E (except use 2015 in E). Repeat for 2016.

Then select columns D and E, and insert a pivot table. Use column D as the row field, column E as the column field, and column D as the data field.  You will get a table that shows which years each combination appeared.

Answer 2

Thank you, this worked perfect! Good to learn something new every day.