Home

Subtracting specific individuals (hundreds) on one list from another

%3CLINGO-SUB%20id%3D%22lingo-sub-683949%22%20slang%3D%22en-US%22%3ESubtracting%20specific%20individuals%20(hundreds)%20on%20one%20list%20from%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-683949%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%26nbsp%3B%20list%20of%20100%2C000%20patients%20that%20were%20invited%20to%20a%20study.%20Of%20these%2C%203%2C000%20signed%20up%20and%20500%20requested%20they%20not%20be%20contacted%20again.%20I%20have%20a%20new%20list%20of%2060%2C000%20patients%20%2C%20with%20some%20overlap%20from%20the%20first%20list.%26nbsp%3B%20So%20before%20I%20invite%20patients%20from%20the%20new%20list%2C%20I%20need%20to%20remove%20those%20who%20previously%20consented%26nbsp%3B%20and%20those%20who%20previously%20asked%20not%20to%20be%20contacted.%26nbsp%3B%20As%20they%20are%20patients%2C%20they%20have%20unique%20identifiers%20associated%20with%20each%20name.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20see%20how%20to%20remove%20duplicate%20line%20items%2C%20but%20not%20how%20to%20subtract%2Fremove%20completely%20patients%20on%20one%20list%20from%20another%20when%20I%20have%20so%20many%20to%20subtract.%26nbsp%3B%20If%20it%20was%2010%20or%20so%20patients%2C%20I%20can%20do%20that%20manually%2C%20but%20not%20500%20or%203000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-683949%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-686247%22%20slang%3D%22en-US%22%3ERe%3A%20Subtracting%20specific%20individuals%20(hundreds)%20on%20one%20list%20from%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-686247%22%20slang%3D%22en-US%22%3EYou%20could%20add%20a%20column%20to%20the%20second%20list%20using%20the%20MATCH%20function%20matching%20the%20ID%20of%20the%20current%20row%20to%20the%20ID%20column%20of%20the%20first%20list%3A%3CBR%20%2F%3E%3DMATCH(A2%2CSheet2!%24A%242%3A%24A%243001%2C0)%3CBR%20%2F%3ENext%20filter%20the%20new%20column%20and%20UNCHECK%20the%20%23N%2FA%20entry.%20THen%20delete%20all%20visible%20rows%20and%20unfilter%20the%20list.%3C%2FLINGO-BODY%3E
Samrrah
Occasional Visitor

I have a  list of 100,000 patients that were invited to a study. Of these, 3,000 signed up and 500 requested they not be contacted again. I have a new list of 60,000 patients , with some overlap from the first list.  So before I invite patients from the new list, I need to remove those who previously consented  and those who previously asked not to be contacted.  As they are patients, they have unique identifiers associated with each name. 

 

I can see how to remove duplicate line items, but not how to subtract/remove completely patients on one list from another when I have so many to subtract.  If it was 10 or so patients, I can do that manually, but not 500 or 3000.

 

Thank you.

1 Reply
You could add a column to the second list using the MATCH function matching the ID of the current row to the ID column of the first list:
=MATCH(A2,Sheet2!$A$2:$A$3001,0)
Next filter the new column and UNCHECK the #N/A entry. THen delete all visible rows and unfilter the list.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies