Finding IDs from one Excel that are missing in a different Excel

kjgarbutt

New Member
Joined
Dec 22, 2018
Messages
6
I have been given some wonderfully complicated and not at all well formatted Excel spreadsheets at work. These spreadsheets are kept by the nurses I work with and have important patient info in. I'm trying to consolidate the data and then clean it in a hope to make everyone's lives easier.

So Excel #1 looks like this and has info on every patient in a study. But it is missing lots of data.

IDDOBConsentSurgery TypeDisease Stage
EX-000101/01/1970YLaser Lap1
EX-000202/02/1971YDiag Lap2
...............
EX-099731/12/1999NRx Endo1

<tbody>
</tbody>

Excel #2 looks like this and has a subset of patients but has a lot more data (eg red column).
IDDOBConsentDisease TissueSurgery Type
EX-000101/01/1970Y1Laser Lap
EX-000404/04/1974N1,2Rx Endo
...............
EX-099630/12/1999N2,4Laser Lap

<tbody>
</tbody>

I am trying to find all the IDs that are in #1 but NOT in #2 . So in the example above, EX-0002 and EX-0997 would be highlighted. Ideally the rows for the missing IDs would be left and everything else removed.

The columns are ordered differently in both spreadsheets and shared info is formatted differently. #2 has more up to date info and more data in general and I am hoping to add the data from #1 to #2 and then populate the missing info.

I've noticed that these kinds of tasks are regularly done by the nurses and take up so much of their time. They're often given a list of IDs in an email and asked for a subset of columns for those IDs and the nurses usually do it by hand and have to take info from one spreadsheet and info from another and then type it all up again.

Any help would be much appreciated!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
Hi there, you could use conditional formatting here if you would like to just simply highlight what is in sheet1 compared to sheet2 based on the ID. Highlight the ID column in sheet1 starting at EX-0001 in your example down to the last row. Click conditional formatting and select "Manage rules". Make sure "show formatting rules for:" shows "current selection". Click the new rule button and click "Use a formula to determine which cells to format". Use a formula similar to this:

Code:
=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$A$1000; 1; FALSE); 5) = 5

Then click the format button, then the fill tab, and select the color you would like it to fill if this condition is filled. Click OK then Apply.

In the formula, it assumes the "#2" in your example is on Sheet2. It also assumes there are 1000 rows. Both of these can be easily adjusted. The 5 is just an arbitrary number I chose.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,791
you can try PowerQuery (Get&Transform)

Code:
[SIZE=1]let
    Source = Table.NestedJoin(Table9,{"Disease Stage", "ID", "DOB", "Consent", "Surgery Type"},Table10,{"Disease Tissue", "ID", "DOB", "Consent", "Surgery Type"},"Table10",JoinKind.LeftAnti)
in
    Source[/SIZE]

IDDOBConsentSurgery TypeDisease StageIDDOBConsentSurgery TypeDisease Stage
EX-0001
01/01/1970​
YLaser Lap
1​
EX-0002
02/02/1971 00:00​
YDiag Lap2
EX-0002
02/02/1971​
YDiag Lap
2​
EX-0997
31/12/1999 00:00​
NRx Endo1
EX-0997
31/12/1999​
NRx Endo
1​
IDDOBConsentDisease TissueSurgery Type
EX-0001
01/01/1970​
Y
1​
Laser Lap
EX-0004
04/04/1974​
N1,2Rx Endo
EX-0996
30/12/1999​
N2,4Laser Lap
 

Watch MrExcel Video

Forum statistics

Threads
1,109,377
Messages
5,528,333
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top