Need a Formula Solution

%3CLINGO-SUB%20id%3D%22lingo-sub-2139916%22%20slang%3D%22en-US%22%3ENeed%20a%20Formula%20Solution%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2139916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20two%20worksheets%20of%20patient%20data%20in%20excel.%20Each%20list%20has%20patient%20records%20and%20one%20of%20the%20values%20in%20each%20is%20a%20patient%20account%20number.%20One%20list%20is%20those%20who%20are%20deceased.%20I%20need%20to%20find%20and%20match%20the%20account%20numbers%20that%20are%20in%20the%20deceased%20list%20and%20remove%20them%20from%20our%20main%20list.%20I%20tried%20the%20CountIf%20function%20but%20I%20couldn't%20get%20it%20to%20work.%20It%20would%20be%20fine%20if%20it%20gave%20me%20a%20%221%22%20in%20a%20new%20column%20to%20signify%20a%20match%20because%20I%20can%20then%20just%20sort%20and%20delete%20any%20of%20them%20with%20a%201.%20But%20again%2C%20I%20couldn't%20get%20it%20to%20work.%20Also%2C%20the%20number%20match%20has%20to%20be%20EXACT%20because%20some%20would%20be%20for%20example%2014558%20and%20another%20may%20be%20145588%20and%20that%20wouldn't%20be%20a%20match%20for%20my%20case.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2139916%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I have two worksheets of patient data in excel. Each list has patient records and one of the values in each is a patient account number. One list is those who are deceased. I need to find and match the account numbers that are in the deceased list and remove them from our main list. I tried the CountIf function but I couldn't get it to work. It would be fine if it gave me a "1" in a new column to signify a match because I can then just sort and delete any of them with a 1. But again, I couldn't get it to work. Also, the number match has to be EXACT because some would be for example 14558 and another may be 145588 and that wouldn't be a match for my case.

 

 

1 Reply

@mmestas 

Let's say the patient list is on Sheet 1, with account numbers in column A.

And the list of deceased patients is on Sheet 2, also with account numbers in column A, in A2:A1000.

 

In the first empty column on Sheet 1, enter Deceased in row 1.

In row 2, enter the formula =ISNUMBER(MATCH(A2, 'Sheet 2'!$A$2:$A$1000, 0))

Fill down from row 2 as far as needed.

You can filter the new column for TRUE, then delete the filtered rows.