Search code examples
excelcompareconditional-formatting

Compare two rows with conditional formatting but "offset" result

I'm trying to compare and highlight all the rows that have the same value (Column G) after each other. My current formula is =$G2=$G3 for the conditional formatting, see green color.

This is my result:

enter image description here

My issue:

I would like to offset the highlight result, so that always the "second result" is marked. So if G4=G5, then I want G5 to be highlighted. Now I only get G4 is marked.

Is it possible to offset the result by one row or some other suggestion? I've tried to use =IF($G2=$G3;OFFSET(G2;1;0)). I also used a dummy column (Column I) to offset the result and base the conditional formatting upon, but would be neat to have it in the formula.

enter image description here

Table

| Index | Account     | Date       | TransType | SymbolName | Qty | Price | Fee   |       |
|-------|-------------|------------|-----------|------------|-----|-------|-------|-------|
| 119   | Portfolio A | 2015-03-30 | DIV       | AA         | 66  | 0,05  | 0,99  | FALSE |
| 120   | Portfolio A | 2015-04-28 | DIV       | BAC        | 42  | 0,23  | 2,898 | FALSE |
| 121   | Portfolio A | 2015-05-15 | Buy       | MSFT       | 42  | 16,58 | 12,95 | TRUE  |
| 123   | Portfolio B | 2015-05-15 | Buy       | MSFT       | 42  | 16,58 | 12,95 | FALSE |
| 122   | Portfolio B | 2015-05-15 | Deposit   | * Cash     | 1   | 732   |       | FALSE |
| 131   | Portfolio A | 2015-08-03 | DIV       | APPL       | 42  | 0,18  | 2,26  | TRUE  |
| 132   | Portfolio B | 2015-08-03 | DIV       | APPL       | 42  | 0,18  | 2,26  | FALSE |
Solution

  • Well, you may try in this was as shown in the image below,

    • Select the whole range, i.e from A2:H8 as shown in the above post

    • From Home Tab --> Click Conditional Formatting Under Styles Group

    • Click New,

    • New Formatting Rule Dialog Box Opens,

    • Select the Rule --> Use A Formula To Determine Which Cells To Format

    • Place the below formula, in the edit rule description

    =($D2=$D1)*($G2=$G1)=1

    • Click Format --> Choose preferred Font or Fill Color From Font or Fill Tab

    • Press OK --> OK

    FORMULA_SOLUTION