How to hide rows if one of the cells is a duplicate of the one above it

Question

I'm trying to filter a contact list that contains each contact's name, company, email address, etc. As it currently is, the contacts are sorted by their company name and I have various contacts from the same company. I want to be able to have only one contact from each company.

Here's how the Google spreadsheet looks: . The company names appear in column G. Notice how "23andMe" appears several times? This is because the first 5 contacts work at 23andMe. I only want to have 1 of those contacts from that company in this list. Throughout the rest of the spreadsheet, I have numerous contacts at the same company.

How can I view only one contact per company? Should the formula hide the row if the cell in column G (the company column) is a duplicate of the cell above it? If so, what is this formula?

Fixed your tags - there are separate labels for google apps – Tim Williams Oct 14 '14 at 20:53 — Tim Williams, Oct 14 '14 at 20:53
Thanks Tim for helping Adam and I. – Gabriel Rotman Oct 15 '14 at 15:53 — Gabriel Rotman, Oct 15 '14 at 15:53

score 3 · Accepted Answer · answered Oct 15 '14 at 02:17

One method would be to use a helper column, and then apply the filter tool to the data. So something like this in row 1 of a spare column:

=ArrayFormula(IF(ROW(G:G)=1,"Display",IFERROR(ROW(G:G)=MATCH(G:G,G:G,0))))

which will apply TRUE and FALSE values according to whether that row should be hidden or not. Apply the filter (funnel icon second from the right on the toolbar) to the whole data set, and you can toggle the "Display" column to only displaying TRUE values.

How to hide rows if one of the cells is a duplicate of the one above it

1 Answers1