Home

Assistance on formula for multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-676530%22%20slang%3D%22en-US%22%3EAssistance%20on%20formula%20for%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676530%22%20slang%3D%22en-US%22%3E%3CP%3EI%20inherited%20a%20huge%20file%20where%20I%20have%20to%20compare%20data%20across%26nbsp%3Bmultiple%20columns%3A%20i.e.%20patient%20name%2C%20date%20of%20service%2C%20etc.%20The%20two%20fields%20I%20really%20need%20to%20focus%20on%20are%20the%20patient's%20name%20and%20the%20date%20of%20service%20-both%20columns%20have%20to%20be%20an%20exact%20match%20to%20determine%20if%20there%20is%20a%20duplicate.%20Using%20Conditional%20Formatting%20it%20highlights%20everything%20that%20is%20a%20duplicate%20even%20if%20the%20patient%20name%20is%20not%20but%20the%20date%20of%20service%20is.%20Is%20there%20an%20easy%20formula%20to%20compare%20data%20for%20patient%20names%20in%20column%20A%20and%20also%20find%20any%20duplicates%20of%20the%20same%20patient%20with%20multiple%20dates%20of%20service%3F%20I'm%20reviewing%20the%20file%20for%20patients%20who%20may%20have%20been%20charged%20multiple%20visits%20on%20the%20same%20date.%20Any%20help%20or%20guidance%20is%20greatly%20appreciated.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-676530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676561%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20on%20formula%20for%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676561%22%20slang%3D%22en-US%22%3EHello%20there.%20You%20can%20use%20a%20helper%20column%20for%20this.%20Insert%20a%20new%20column%20into%20your%20data%20set.%20Assuming%20for%20the%20moment%20that%20Patient%20Name%20is%20in%20column%20A%20and%20Date%20of%20Service%20is%20in%20column%20B%20and%20your%20new%20helper%20column%20is%20column%20C%20with%20headers%20in%20row%201%2C%20then%20in%20C2%20your%20formula%20would%20be%3A%3CBR%20%2F%3E%3DA2%26amp%3BB2%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20you%20could%20add%20conditional%20formatting%20to%20highlight%20duplicates%20(Home%20%26gt%3B%20Conditional%20Formatting%20%26gt%3B%20Highlight%20Cells%20Rule%20%26gt%3B%20Duplicate%20Cells...%20).%20This%20will%20highlight%20all%20duplicates%2C%20including%20the%20first%20one.%20If%20you%20wanted%20to%20not%20highlight%20the%20first%20duplicate%20but%20all%20others%20below%20it%2C%20you%20could%20use%20a%20custom%20formula%20for%20your%20conditional%20formatting%2C%20such%20as%3A%3CBR%20%2F%3E%3DCOUNTIF(C%242%3AC2%2CC2)%26gt%3B1%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20above%20formula%20assumes%20you've%20selected%20C2%20through%20the%20last%20row%20of%20data%20in%20C2.%20The%20important%20part%20is%20the%20range%20is%20relative%20to%20what%20you%20have%20selected.%20So%20if%20you%20select%20the%20entire%20column%2C%20with%20C1%20being%20the%20active%20cell%20when%20applying%20the%20conditional%20format%20formula%2C%20the%20formula%20would%20be%20adjusted%20to%3A%3CBR%20%2F%3E%3DCOUNTIF(C%241%3AC1%2CC1)%26gt%3B1%3CBR%20%2F%3E%3CBR%20%2F%3EHTH%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677986%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20on%20formula%20for%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677986%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20-this%20helped%20tremendously!%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F19137%22%20target%3D%22_blank%22%3E%40Zack%20Barresse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677991%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20on%20formula%20for%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677991%22%20slang%3D%22en-US%22%3EYou're%20very%20welcome.%20%3A)%3C%2FLINGO-BODY%3E
stygian73
New Contributor

I inherited a huge file where I have to compare data across multiple columns: i.e. patient name, date of service, etc. The two fields I really need to focus on are the patient's name and the date of service -both columns have to be an exact match to determine if there is a duplicate. Using Conditional Formatting it highlights everything that is a duplicate even if the patient name is not but the date of service is. Is there an easy formula to compare data for patient names in column A and also find any duplicates of the same patient with multiple dates of service? I'm reviewing the file for patients who may have been charged multiple visits on the same date. Any help or guidance is greatly appreciated. Thank you.

3 Replies
Hello there. You can use a helper column for this. Insert a new column into your data set. Assuming for the moment that Patient Name is in column A and Date of Service is in column B and your new helper column is column C with headers in row 1, then in C2 your formula would be:
=A2&B2

Then you could add conditional formatting to highlight duplicates (Home > Conditional Formatting > Highlight Cells Rule > Duplicate Cells... ). This will highlight all duplicates, including the first one. If you wanted to not highlight the first duplicate but all others below it, you could use a custom formula for your conditional formatting, such as:
=COUNTIF(C$2:C2,C2)>1

The above formula assumes you've selected C2 through the last row of data in C2. The important part is the range is relative to what you have selected. So if you select the entire column, with C1 being the active cell when applying the conditional format formula, the formula would be adjusted to:
=COUNTIF(C$1:C1,C1)>1

HTH

Thank you -this helped tremendously! @Zack Barresse 

You're very welcome. :)