Forum Discussion
Identify duplicate values based on another column
=IF(COUNTA(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2)))>1,TRANSPOSE(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2))),"")
You are welcome. This formula should return the required result. For example email3 occurs two times in database A and isn't recognised as a duplicate. Email2 is twice in database A and once in database C as is returned as a duplicate.
For example if in database A an email exists twice, I don't need to know that. But if an email exists twice in database A and once in database C it should be highlighted.
=IF(COUNTA(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2)))>1,TRANSPOSE(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2))),"")
You are welcome. This formula should return the required result. For example email3 occurs two times in database A and isn't recognised as a duplicate. Email2 is twice in database A and once in database C as is returned as a duplicate.
I'd be glad to help you with the formula and understand the email3 and email2 results you're seeing.
Formula Breakdown:
The formula you provided, =IF(COUNTA(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2)))>1,TRANSPOSE(UNIQUE(FILTER($B$2:$B$13,$D$2:$D$13=D2))),""), is used to identify and list unique values in column B (range $B$2:$B$13) that appear more than once in rows where column D (range $D$2:$D$13) has the same value as the value in cell D2. Here's a step-by-step explanation:
- FILTER($B$2:$B$13,$D$2:$D$13=D2): This part filters column B based on the condition that the corresponding values in column D are equal to the value in cell D2.
- UNIQUE(...): This removes duplicate values from the filtered range.
- COUNTA(...): This counts the number of unique values remaining after the UNIQUE function.
- >1: This checks if the number of unique values is greater than 1 (meaning there are duplicates).
- TRANSPOSE(...): If duplicates exist, this part transposes the unique values into a column format.
- "": If no duplicates are found, an empty string ("") is returned.
Possible Explanation for email3 Not Being Recognized:
There are a few possibilities why email3 might not be considered a duplicate:
- Case Sensitivity: If your data is case-sensitive (e.g., "Email3" is different from "email3"), the formula might not recognize them as duplicates. Ensure consistent casing throughout your data.
- Special Characters: If email3 contains special characters that are not present in other email addresses, the formula might treat them as distinct values. Check for any inconsistencies in formatting or characters.
- Data Range: Make sure the formula's range ($B$2:$B$13 and $D$2:$D$13) encompasses all relevant email addresses. If email3 is outside this range, it won't be considered.
Clarifying email2 Results:
Without more information about your data, it's difficult to pinpoint the exact reason why email2 might be considered a duplicate in some cases but not others. However, here are some potential explanations:
- Multiple Occurrences: If email2 appears twice in Database A and once in Database C, it would be considered a duplicate based on the formula's logic, even though it appears in different databases.
- Database C Filter: If Database C has a filter applied that excludes the second occurrence of email2, the formula might not be able to access it and therefore not consider it a duplicate.
- Data Inconsistencies: If there are inconsistencies in how email addresses are formatted or stored, the formula might not identify them as duplicates correctly.
I'll do my best to help you understand the formula's behavior
