My spreadsheet (MS Excel 365) has about 10 columns and 5k rows, but for the sake of simplifying it let’s consider the 2 columns I need to work with.
Column A has number values, column B has text and is sometimes blank.
Column B sometimes also repeats (only twice). These repetitions don’t always occur in consecutive rows.
What I need is, when a repetition occurs, to have a helper column that tells me which of the 2 has the greater value in column A.
I’ve tried COUNTIF but I got nowhere with it.. all that got me was different values in C, which was useless considering there are dozens of repetition instances..
EDIT: I’m adding an example as advised below. (thank you!)
C is what I would like it to show:
- 1 if there is no repetition from column B
- 1 if there is and A holds the greater value
- 2 if there is and A the lower value
| A | B | C |
|---|---|---|
| 35 | red | 2 |
| 14 | blue | 1 |
| 123 | yellow | 1 |
| 44 | red | 1 |
| 455 | green | 1 |
| 112 | green | 2 |
| 3 | blue | 2 |