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!
 

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
3,219
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
 

Forum statistics

Threads
1,077,784
Messages
5,336,327
Members
399,076
Latest member
vullistax

Some videos you may like

This Week's Hot Topics

Top