workbook comparing

[Steve Kay]

Hi Everyone,

Can anyone help? I am looking to compare to worksheets.
Both worksheets have column labelled 'patient number' and
the number is a four digit number. I would like to
compare the two worksheets and gather a list of patients
that are not on both sheets. Any assistance is helpful,
there are over 1000 paients in one worksheet and 800 in
the other... so the results would be at least 200
patients. Thank You!

 

[Ken Wright]

One easy way:-

With a COPY of your data from Sht1 on another sheet in any column, put Sht1 in
the cell next to it and copy down as far as it goes, eg:-
(Put Two Headings in as well)

PAT SHEET
1111 Sht1
1234 Sht1
2314 Sht1
2312 Sht1
3421 Sht1

Now copy the data from the second sheet directly underneath it, and put Sht2 in
the cell next to it and copy down:-

PAT SHEET
1111 Sht1
1234 Sht1
2314 Sht1
2312 Sht1
3421 Sht1
1123 Sht2
1124 Sht2
2214 Sht2
2312 Sht2
3411 Sht2

Now select all the data and do Data / Pivot Table and Pivot Chart Report

Drag PAT into the ROW fields on the left
Drag SHEET into the COLUMN fields at the top
Drag SHEET into the DATA area as well

The data should default to COUNT for the data in the DATA area and you should
now see a list of all patient numbers in order with gaps where they don't appear
on the relevant sheets. That give syou what you want, but lets make it easier


Now, select the entire table and do Edit / Copy, then Edit / Paste Special
Values
With the data still selected, do data / Filter / Autofilter and you will see
little arrows appear at the top in each cell
On the TOTAL column at the end, click on the arrow and choose 1

You will now see a filter of records where only 1 record appears, meaning it is
on one sheet but not the other.