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.