Search code examples
excel-formulaexcel-365

How to compare two rows for variable data, using a formula

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.

Solution

  • I think you are looking for something like this?

    Formula in F3 is below and can be filled down:

    =IF(AND((ISNUMBER(XMATCH(B2:D2,B3:D3,0,1))+(B2:D2=0))>0),"NEG","POS")

    ISNUMBER(XMATCH(B2:D2,B3:D3,0,1))+(B2:D2=0) checks whether each marker in row2 is either in row3, or is 0.

    If all markers in row2 is available in row3 or is just 0, then row3 is NEG, otherwise POS.

    enter image description here