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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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
4,670
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,089,376
Messages
5,407,903
Members
403,169
Latest member
Luna17

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top