My table is as follows (although this is a sample, the actual table is 12Rx20C):
| Order | Markers | Markers | Markers | Result |
|---|---|---|---|---|
| 50K | Marker A | 0 | 0 | NEG |
| 50K | Marker A | 0 | 0 | NEG |
| 601 | Marker A | Marker B | 0 | NEG |
| 95Y | Marker B | Marker C | 0 | POS |
| 95Y | Marker B | Marker C | 0 | NEG |
| M22 | Marker F | 0 | 0 | POS |
| M22 | Marker F | 0 | 0 | NEG |
| K3S | Marker F | 0 | 0 | NEG |
| K3S | Marker F | 0 | 0 | NEG |
| K12 | 0 | 0 | 0 | POS |
| K12 | 0 | 0 | 0 | NEG |
| 0 | 0 | 0 | ||
| 0 | 0 | 0 | ||
| 0 | 0 | 0 |
On the worksheet, there’s also a list of all makers (named list MARKERLIST):
| Marker list |
|---|
| Marker A |
| Marker B |
| Marker C |
| Marker D |
| Marker E |
| Marker F |
| Marker G |
| Marker H |
| Marker I |
Both the order column and Markers columns are filled by a formula.
I’m trying to come up with a formula that fills in the ‘Result’ column. The following rules should apply:
- Adding a marker is not a problem, so NEG (so 50K to 601, marker B is added);
- When a marker is replaced, or dropped, that’s a POS (601 to 97Y, marker A is swapped for marker C/ K3S to K12, marker F is dropped);
- Changing from one set of markers to another is a POS (95Y to M22 is a different set);
- Changing order, but keeping markers, is a NEG (M22 to K3S);
So far I’ve tried a COUNTIFs: =TRANSPOSE(COUNTIFS(B2:B4 , MARKERLIST) for all rows.
Then applying a MAP function between two rows to find differences, along the lines of =MAP(COUNTIFS row 1, COUNTIFS row2, LAMBDA(a, b, IF(a>=b, 0, 1); which is then summed.
But this method gives a lot of false results, especially when a marker(set) appears multiple times in a row.
I tried to add in a SCAN function of the order list, to detect changes over there (one order always has the same marker set), but this still doesn’t completely fix it. =SCAN(0, Ordercolumn, LAMBDA(a, b, IF(b = OFFSET(b, 1, 0), 1, "")
Preferably it would be done in a single column, but helper columns are allowed, although I’d like to keep them to a minimal.
I hope this makes sense, if anything is unclear, let me know and I’ll try to edit in a better explanation.