Adding in data from another database

[Guest]

As a Nursing data analyst, I must combine information from multiple sources.
Each source contains different elements I need to import. Right now I have
several large groups of data that go back to 1999. So each database has about
2000 entries. I have imported all the data to excel in worksheets.

The good news is that each group of data is indexed with a patient medical
record number in the first row. The bad news is that about 5% of the entries
don't have a corresponding medical record number both worksheets. All I want
to do is to add in the data items that have a medical record number on both
sheets. Entries indexed with the medical record that don't exist in both
worksheets can either be skipped or even deleted.

Is there a way to do this either native to excel or some code that someone
has already written? I am fairly new to Excel and Visual Basic. Can someone
point me in the right direction here?

Thanks

 

[Tom Ogilvy]

Assume the medical record number is in column A of each of Sheet1 and sheet2
Assume the first row of each sheet has column headers/labels.

in sheet1, insert a new column B (select Column B, do Edit=>Insert)
Give it a header of something like MATCH

in B2 put in a formula
=if(Countif(Sheet2!$A:$A,$A2)>0,"Keep","Delete")
then drag fill down the column

select cell A1

Do Data=>filter=>Autofilter
in the column B dropdown, select Delete

Now only rows to be deleted are visible. In the gray labels to the left,
select the first visible row below row1 hold down the Shift key, scroll down
to the last visible row and again click in the gray label. This will select
all rows from the first visible to the last visible.

Do Edit=>Delete

this will only delete the visible rows. Now do

Data=>Filter=>Autofilter to remove the autofilter.

Select Column B and do Edit=>Delete

Repeate this for the second sheet where the formula would be

=if(Countif(Sheet1!$A:$A,$A2)>0,"Keep","Delete")

 

[Guest]

Very Cool!

You just saved me about 10 hours of work!

 

[Tom Ogilvy]

I said:

(select Column B, do Edit=>Insert)

I should have said

(select Column B, do Insert=>Column)

 

[Tom Ogilvy]

Glad it worked out for you.

 

[Guest]

WooHoo! worked like a charm . . . : )

I said:

(select Column B, do Edit=>Insert)

I should have said

(select Column B, do Insert=>Column)