Google-sheets – Conditional Formatting Duplicate Invoices in Google Sheets

conditional formattingformulasgoogle sheets

My workplace uses Google Sheets to track invoices across all locations. The two most important columns are next to each other and titled Invoice number and Vendor. I wanted to highlight the Invoice number cell every time there is a duplicate value. I started with =countif(A:A,A1)>1, however, this would highlight any duplicate value, even if the invoices were from two different vendors. Is it possible to format a Google sheet so it will only highlight the duplicate invoice (column F) if and only if columns F and G are both duplicate values?

Best Answer

custom formula for highlighting duplicates should look like this:
```
=COUNTIFS(F:F, F1, G:G, G1)>1
```

Related Solutions

Google-sheets – Split one column into two equal halves

Assuming the data is in column B, the following two formulas do the job. In cell C1:

=filter(B1:B, row(B1:B) < 1 + max(filter(row(B:B), len(B:B))) / 2)

In cell D1:

=filter(B1:B, row(B1:B) >= 1 + max(filter(row(B:B), len(B:B))) / 2)

Explanation:

Find the last row with data by max(filter(row(B:B), len(B:B)))
Divided by 2 and use as a threshold for splitting the column: rows above it go to column C, rows below it go to column D.

Google-sheets – Google Sheets Filter Conditions from List

To filter based on comparison against a large array, I would put that array into the spreadsheet (say, column Z, or somewhere on another sheet) and then use the filter based on match function:

=filter(A2:P, match(P2:P, Z1:Z, 0))

Here, match returns a positive number when a cell value is found in column Z, and returns #N/A! otherwise. The filter interprets positive numbers as "yes, keep this row" and errors as "no, do not keep".

Best Answer

Related Solutions

Google-sheets – Split one column into two equal halves

Google-sheets – Google Sheets Filter Conditions from List

Related Topic