Forum Discussion
Assistance on formula for multiple columns
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 ...
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
=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. :)