Suppose I have a spreadsheet containing grades of several sites given by variable numbers of surveyors each day:
| date | site_a_grade | site_b_grade | site_c_grade |
|---|---|---|---|
| 2021-10-01 | 5 | 4 | 4 |
| 2021-10-01 | 5 | 3 | 4 |
| 2021-10-02 | 5 | 4 | 3 |
| 2021-10-02 | 5 | 4 | 4 |
| 2021-10-02 | 4 | 4 | 4 |
| 2021-10-03 | 5 | 4 | 4 |
Is it possible to automatically compare daily scores from all surveyors for each site and highlight the different cells with colour for easy viewing? For example, the two site_b_grade for 2021-10-01 are different, so they could be highlighted in amber for review. Now I'd like to do this for the whole sheet automatically.
I found a similar question on VBA to compare rows and highlight differences in the same worksheet, where the OP wanted to compare 2 adjacent rows. I guess it's possible to generalise the solution to specify a grouping column, but I don't know VBA, so kind help will be appreciated!
CodePudding user response：
You don't need VBA for this, you can get it done using conditional formatting.
For Example if your sample data above is in range A1:D7, then you can use the following formula on range B2:D7 (excluding headers and 1st column)
=VLOOKUP($A2,$A$1:$D$7,COLUMN(B2)-COLUMN($A2) 1,FALSE)<>B2
This will highlight all the occurrences which do not match the first occurance
Note that the $ symbols are really important
Sample Screenshot:
