Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Matching cells in 2 Excel spreadsheets

Posted on 2011-09-17
Medium Priority
468 Views
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
Question by:Chrisjack001

LVL 50

Accepted Solution

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

cheers, teylyn
0

Author Closing Comment

ID: 36555596
Thanks a lot for your prompt help
0

## Featured Post

Question has a verified solution.

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

I came across an unsolved Outlook issue and here is my solution.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
###### Suggested Courses
Course of the Month14 days, 3 hours left to enroll