Compare two datasets - VBA array maybe?

[TheWennerWoman]

I have two datasets from two different systems.

Dataset A might look like

FINEART-MJ I109024
FINEART-MJ I109025
FRANCOLI-PU I109026

Dataset B might look like

FINEART I109024
FINEART I109025
FRANCOLI I109028

What I need to be able to do is compare dataset A with dataset B and flag anything where the I numbers don't match. I can use LEFT and FIND to tidy dataset A and in the above I would expect the third record to fail (I109026 vs I109028).

There are thousands of these records - I can't use VLOOKUP because it only finds the first record so generates a lot of errors.

Any thoughts appreciated.

 

[saboh12617]

Hello,

In the formula below, adapt the columns to match your dataset positions, and it should give you the expected result.
With exact datasets (the array does not contain empty rows) :

=LET(
  dataA, A2:A4, dataB, B2:B4, 
  GetID, LAMBDA(longN, TAKE(TEXTSPLIT(longN, "I"), , -1)), 
  arrID, HSTACK(BYROW(dataA, GetID), BYROW(dataB, GetID)), 
  FILTER(arrID, CHOOSECOLS(arrID, 1)<>CHOOSECOLS(arrID, 2)))

With potential empty rows :

  =LET(
    dataA, A2:A10, dataB, B2:B10, 
    GetID, LAMBDA(longN, TAKE(TEXTSPLIT(longN, "I"), , -1)), 
    arrID, HSTACK(BYROW(dataA, GetID), BYROW(dataB, GetID)), 
    FILTER(arrID, NOT(ISERROR((CHOOSECOLS(arrID, 1))*(CHOOSECOLS(arrID, 1)<>CHOOSECOLS(arrID, 2))))))