Solved

Matching cells in 2 Excel spreadsheets

Posted on 2011-09-17
2
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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