Google-sheets – Formatting based on multiple cells and duplicates

conditional formattinggoogle sheetsgoogle-apps-script

I will try my best to explain the situation :

I have a sheet where we enter test data and serial numbers.

Column A is serial number
Column C is if the unit passed or not.

I currently have conditionalformatting to highlight duplicate serial numbers (based on Column A).

Because we fix and
retest failed units we get duplicates of serial numbers where one has
pass in column C and the other has fail.

What I would like to do is :
Have the sheet find duplicates but only highlight when both
duplicates have "pass" in column C.

I have tried both google-apps-script and conditional-formatting, I don't know if it is possible.

Best Answer

how about you create another column and concatenate A and C, then do this:

=Countif(D:D,D1)>1

When you concatenate serial and pass/notpassed you have a unique value for serial A-pass and serial A-"not passed" and then you can countif them

Related Solutions

Google-sheets – Conditional formatting based of current cell AND another cell

Pretending that your data starts with id in column A, then B through D, put the conditional formatting range to: 'C3:E' and then choose your argument as custom formula with this:

=And(or(isblank(B:B),isblank(C:C),isblank(D:D)),istext(A:A))

Google Sheets – Import Cells with Specific Conditional Formatting

As Rubén said, conditional formatting is for human consumption only: neither sheet formulas nor scripts can access it. You should create a column that records the fact of "all answers are correct", and then filter by that column. For example, put in E2

=arrayformula((C2:C=2)*(D2:D=4))

which will put 1 in every row where both answers are correct. Then execute query command on the result of importrange:

=query(importrange("...", "'Form Responses 1'!B:E"), "select Col1, Col2, Col3 where Col4 = 1", 1)

Best Answer

Related Solutions

Google-sheets – Conditional formatting based of current cell AND another cell

Google Sheets – Import Cells with Specific Conditional Formatting

Related Topic