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

[kjgarbutt]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]DOB[/TD]
[TD]Consent[/TD]
[TD]Surgery Type[/TD]
[TD]Disease Stage[/TD]
[/TR]
[TR]
[TD]EX-0001[/TD]
[TD]01/01/1970[/TD]
[TD]Y[/TD]
[TD]Laser Lap[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]EX-0002[/TD]
[TD]02/02/1971[/TD]
[TD]Y[/TD]
[TD]Diag Lap[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]EX-0997[/TD]
[TD]31/12/1999[/TD]
[TD]N[/TD]
[TD]Rx Endo[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Excel #2 looks like this and has a subset of patients but has a lot more data (eg red column).
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]DOB[/TD]
[TD]Consent[/TD]
[TD]Disease Tissue[/TD]
[TD]Surgery Type[/TD]
[/TR]
[TR]
[TD]EX-0001[/TD]
[TD]01/01/1970[/TD]
[TD]Y[/TD]
[TD]1[/TD]
[TD]Laser Lap[/TD]
[/TR]
[TR]
[TD]EX-0004[/TD]
[TD]04/04/1974[/TD]
[TD]N[/TD]
[TD]1,2[/TD]
[TD]Rx Endo[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]EX-0996[/TD]
[TD]30/12/1999[/TD]
[TD]N[/TD]
[TD]2,4[/TD]
[TD]Laser Lap[/TD]
[/TR]
</tbody>[/TABLE]

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]

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:

=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]

you can try PowerQuery (Get&Transform)

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]DOB[/td][td=bgcolor:#5B9BD5]Consent[/td][td=bgcolor:#5B9BD5]Surgery Type[/td][td=bgcolor:#5B9BD5]Disease Stage[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]DOB[/td][td=bgcolor:#70AD47]Consent[/td][td=bgcolor:#70AD47]Surgery Type[/td][td=bgcolor:#70AD47]Disease Stage[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]EX-0001[/td][td=bgcolor:#DDEBF7]

01/01/1970​

[/td][td=bgcolor:#DDEBF7]Y[/td][td=bgcolor:#DDEBF7]Laser Lap[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]EX-0002[/td][td=bgcolor:#E2EFDA]

02/02/1971 00:00​

[/td][td=bgcolor:#E2EFDA]Y[/td][td=bgcolor:#E2EFDA]Diag Lap[/td][td=bgcolor:#E2EFDA]2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]EX-0002[/td][td]

02/02/1971​

[/td][td]Y[/td][td]Diag Lap[/td][td]

2​

[/td][td][/td][td]EX-0997[/td][td]

31/12/1999 00:00​

[/td][td]N[/td][td]Rx Endo[/td][td]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]EX-0997[/td][td=bgcolor:#DDEBF7]

31/12/1999​

[/td][td=bgcolor:#DDEBF7]N[/td][td=bgcolor:#DDEBF7]Rx Endo[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]DOB[/td][td=bgcolor:#5B9BD5]Consent[/td][td=bgcolor:#5B9BD5]Disease Tissue[/td][td=bgcolor:#5B9BD5]Surgery Type[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]EX-0001[/td][td=bgcolor:#DDEBF7]

01/01/1970​

[/td][td=bgcolor:#DDEBF7]Y[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Laser Lap[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]EX-0004[/td][td]

04/04/1974​

[/td][td]N[/td][td]1,2[/td][td]Rx Endo[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]EX-0996[/td][td=bgcolor:#DDEBF7]

30/12/1999​

[/td][td=bgcolor:#DDEBF7]N[/td][td=bgcolor:#DDEBF7]2,4[/td][td=bgcolor:#DDEBF7]Laser Lap[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]