0

Table Data:

Table Data

This is a dulled down version of the Data Table I am trying to filter - which is a massive list of leads.

Basically, I am trying to find a way to combine all rows with similar contact information. Since addresses, phone numbers, and emails are unique identifiers (Meaning only one person or residence can have any given address, phone number, or email), those are the columns I will be using as filter rules.

The current formula I have will create an array of data - combining all data in column A - if at least one of the values from Columns B-D match.

This results in an almost perfect result:

Filtered Table

This filtered table outputs Duplicate if at least one of the values within Columns B:D are not Unique. The issue here is the formula is only looking for duplicates on a 2D scale - explaining why there are two Andrew 2's and Evan 1's.

So in short, Andrew 2 is shown twice because Rows 3 and 6 share the same values in Column C and Rows 4 and 6 share the same values in Column D.

For this particular case, since Andrew 2 has the same phone number as Walter 1 - but the same email as Trey 1 and Evan 1 - the goal is use formulas to assume Walter 1 is the same contact as Trey 1 and Evan 1 based on both of their relationships with Andrew 2. The end result would be Walter 1 listed in Row 4, or of equivalence. Extremely complex, but nonetheless I need to figure this out.

I have put well over 12 hours into trying to figure out how to output each name only once and completely combine the data I need correctly, but I simply am at a stand still.

I have two formulas that output the same results shown in the second table:

Filter Formula (Pasted into G2 and copied down Column G):

=IF(AND($B2 <> "", $C2 <> "", $D2 <> ""),IF(AND(COUNTIF($B$2:$B2, $B2) = 1,COUNTIF($C$2:$C2, $C2) = 1,COUNTIF($D$2:$D2, $D2) = 1),Transpose(SORT(UNIQUE(FILTER($A:$A,($B:$B = $B2)+($C:$C = $C2)+($D:$D = $D2))), 1, TRUE)),"Duplicate"),"")

Query Formula (Pasted into G2 and copied down Column G):

=IF(AND($B2 <> "", $C2 <> "", $D2 <> ""),IF(AND(COUNTIF($B$2:$B2, $B2) = 1,COUNTIF($C$2:$C2, $C2) = 1,COUNTIF($D$2:$D2, $D2) = 1),TRANSPOSE(UNIQUE(QUERY(A:D,"Select A where (B = '"&B2&"') OR (C = '"&C2&"') OR (D = '"&D2&"') label A ''",1))),"Duplicate"),"")

Here is a copy of my sheet if you would like to experiment on there. I have random formulas all over the place because I've been experimenting with this a lot. Thank you in advance for any help. https://docs.google.com/spreadsheets/d/1b2kaI-Muuf7V7CDBvdhsorSzqKmKJdQ_TJsBoQX26GM/edit?usp=sharing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.