Solved

Matching cells in 2 Excel spreadsheets

Posted on 2011-09-17
2
433 Views
Last Modified: 2012-05-12
I have 2 excel spreadsheets that have the same patient names. In the spreadsheet called “ClarityScheduling_CTRC_Formatted” Column H which is the “StudyID” field is blank and I want to update that column from column C “Study ID” field of the other spreadsheet called “Clarity-CTRC Combination “ by matching the patient name and the date of Appointment together and then copying and pasting the corresponding “Study ID” from column C of the “Clarity-CTRC Combination.xls “ to column H of “ClarityScheduling_CTRC_Formatted.xls”  I have done a couple of examples high light for you to see what I am trying to accomplish with 1173 records. The question is how can I accomplish copy and pasting the correct study IDs to the other spreadsheet.  Attached are the 2 excel spreadsheets. Thanks in advance.
Clarity-CTRC-Combination.xlsx
ClarityScheduling-CTRC-Formatted.xlsx
0
Comment
Question by:Chrisjack001
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 36555572
Hello,

with both workbooks open, enter this formula into cell H2 of the target workbook

=INDEX('[Clarity-CTRC-Combination.xlsx]Combination'!$C$2:$C$1175,MATCH(B2&D2,INDEX('[Clarity-CTRC-Combination.xlsx]Combination'!$A$2:$A$1175&'[Clarity-CTRC-Combination.xlsx]Combination'!$B$2:$B$1175,0),0))

Copy down.

Some cells will show "#N/A" because the combination of name and date is not found in the source file.

To show something other than N/A for these cells, wrap the formula in IFERROR, like this

=IFERROR(INDEX('[Clarity-CTRC-Combination.xlsx]Combination'!$C$2:$C$1175,MATCH(B2&D2,INDEX('[Clarity-CTRC-Combination.xlsx]Combination'!$A$2:$A$1175&'[Clarity-CTRC-Combination.xlsx]Combination'!$B$2:$B$1175,0),0)),"not found")

cheers, teylyn
0
 

Author Closing Comment

by:Chrisjack001
ID: 36555596
Thanks a lot for your prompt help
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL multicriteria from ONE textbox 32 42
Excel Question 17 15
Microsoft Office Licensing Question 2 27
MS Access Delete All Excel sheets 1 8
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question