# Combining Duplicate Data Within A Table? | Google Sheets

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:

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