Matched column in a new sheet

Here's the scenario that I want to achieve:

1. Match File A column "A" (MRN) with File B column "A" (MRN)
2. Only similar matches in each will be reflected in a new sheet with the following columns
- MRN
- Day of stay (file A)
- Worktype
- Doctor Name
- Date D. Created
- Date D. Completed

Basically I need a shorter way to work on these 2 files as comparing them manually takes ages to finish. I need at least to automate the process. Any help is highly appreciated.

I attached the 2 xls files for reference.

Hello iamresse,

I have both workbooks open. I see most of the column headings expect MRN, and WorkType. Which sheets are these on and which columns do they refer to?

opps sorry about that. I am reattaching my files...

Hello iamreese,

Thanks for posting the updated files. That makes it clear now.

Do you need this data sorted?
Can the combined data be saved in one of the workbooks which is already open?

Hello iamreese,

The new worksheet would like sheet 3 in workbook "B" with an extra column for the day of stay?

I updated my file. I need to match Column A with Column D. If a match is found, it will put the put the result in same row including columns E to J.

For example, A3 is matched with D792. The result should be like this:

432040571 30/09/2011 01/10/2011 Discharge Summary 1102011 Al Hossni Yahya Mohammad 01/10/2011 18:25 02/10/2011 10:04

Any takers anyone? badly need help on this..

I simplified my sample data

Hello iamreese,

I would have had this done last night. However, my computer decided to go on strike and show me the Blue Screen of Death while working on your project. Most of the work was already saved.

Someone was able to look at your newest attachment. Unfortunately, I can not. I will need to contact Roy (the system admin) to see what is wrong. To double check this not on my end, see if you can download the Discharges workbook.

Hello iamreese,

Lol, mystery solved! Looks my ISP is a little slow to update this morning. I do have a few questions about the worksheets.

Are these worksheet names the same as in the original file?
What do you want to name "Sheet3"?
Are all the headers in row 1?

Hello iamreese,

I have a situation that requires some clarification. Patient ID 432046622 occurs 6 times on the discharge sheet but only once on the Dictation sheet. Should I apply the Dictation data to all 6 items on the Discharge sheet?

Please Login or Register  to view this content.

Hello iamreese,

This macro looks to me to be correct. However, after many hours of checking and rechecking, it is good for someone else to take a fresh look. Here is the macro that has been added to the attached workbook.

Please Login or Register  to view this content.

Hello iamreese,

If you expand your rows on both sheets then no changes will need to be made to macro. If you change the columns then I will need to make adjustments the macro.

Hello iamreese,

On the workbook I posted with the macro, it ran in less than second on my computer. Do you have forumlas in the workbook that the macro is running in? If so, formula claculation should be suspended until the macro has finished running.

Hello iamreese,

If there will multiple entries on Dictation for a patient then I will need to make some changes to the macro.

How will the multiple Dictation entries be applied to the Discharge entries?

The column name will look like this:


Patient ID Admission Date Discharge Date Day of Stay WORKTYPE DICTATOR NAME Date D. Created Date T. Completed WORKTYPE DICTATOR NAME Date D. Created Date T. Completed

Duplicate will be copied in columns I, J, K, L in Discharge worksheet (WORKTYPE DICTATOR NAM Date D. Created Date T. Completed)


If more than 2 matches were found then it will be in M, N, O, P columns

I attached the test file with the macro and columns for additional matches.