I need a formula that compares the values of 2 different cells in 2 different columns (same row) to see if there are duplicate values in the array, with those same 2 values. I could easily do this by concatenating the 2 values and running a countif to see if there is more than 1 instance of it, but I don't want to do that. I have one pre-defined column that I need this formula to go into, so I want one formula that does this.
I cannot use vba, conditional formatting, filtering, etc.. on this. It needs to be one self-contained formula. (The spread sheet it will go into; there's hundreds of them, thousands of rows, etc... I just need something to quickly identify the duplication with no additional work for the user.)
I just need to know if those two values together both duplicate somewhere else in my spreadsheet; the number of times duplicated is not an issue.
I.E.
I have columns A and B.
Column A has 30 values, numbered 1, 2 and 3, ten duplicates each. Column B has random dates. I want to know if any of those rows contain both a matching date and a matching group number. If 1/1/2013 appears 3 times and is in groups, 1, 2, and 3, then I don't care. If it appears 3 times, all in group 3, then I want to identify those.
Any suggestions/solutions would be most appreciated. Thanks!
Book1.xlsx
I added a sample workbook, if this helps. The ones that I highlighted are the ones that I would want the formula to catch; where both cell values are the same.
Bookmarks