Forum Discussion

LachlanB98's avatar
LachlanB98
Copper Contributor
Jun 03, 2024

Extracting table values if values in two columns have identical values in other rows

  Hi guys,   Can someone please help me with a formula for the following task?   I have over 40,000 rows of data in 'Table 1', I've extracted a sample.   I need to analyse only cells which con...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 04, 2024

djclements 

Great point.

However, if use XMATCH with binary search it works fast

=LET(
  pd,  product & "| " & decrement,
  u,   SORT( UNIQUE(pd,,1) ),
  f,   FILTER( table, ISNA( XMATCH(pd,u,,2) ) ),
  VSTACK( {"ID NUMBER","PRODUCT","DECREMENT","IN USE?"}, f )
)
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 04, 2024

SergeiBaklan 

Well spotted!  And I think the formula wins the time trial.  When I was timing the original formula with helper ranges the calculation was taking about 230ms.  Combining them into

= LET(
    prodDecr,    product & "|" & decrement,
    exactlyOnce, UNIQUE(prodDecr,, TRUE),
    multiple?,   ISNA(XMATCH(prodDecr, exactlyOnce)),
    FILTER(Table1, multiple?)
  )

and the time required leapt to 8000ms!  Forcing the binary search and the time reduced to 150ms.  I am so used to the idea that XLOOKUP and XMATCH perform sorts as part of the solution process to gain efficiency that this came as a surprise.

 

Once as I had come to terms with the fact that COUNTIFS does not perform well on datasets of this size (10,000ms), I tried a different strategy of sorting the dataset and comparing each record with prior and subsequent records to determine the filter criterion.

=LET(
    sortedTbl,     SORT(Table1, {2,3}),
    CurrentPD,     CHOOSECOLS(sortedTbl,2,3),
    priorPD,       DROP(VSTACK({0,0}, CurrentPD),-1),
    nextPD,        DROP(VSTACK(CurrentPD,{0,0}),1),
    matchesnext,   BYROW(CurrentPD = nextPD, AND),
    matchesPrior,  BYROW(CurrentPD = priorPD, AND),
    matchesEither, MAP(matchesPrior, matchesnext, OR),
    FILTER(sortedTbl, matchesEither)
  )

That was back down to 250ms so, no prizes, but a pretty credible performance!

 

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 05, 2024

    PeterBartholomew1 

    Thank you for sharing performance test results. I had approximately the same result, but used simpler method for calculation time, like

    =LET(
   start, NOW(),
   result,...,
   end, NOW(),
   VSTACK( result, TEXT(end-start, "ss.000" )
)

    XMATCH sorts nothing inside, my assumption it takes a lot of time to map found position in sorted array back on unsorted one. That's end user job if use binary search.

    Bit surprized that BYROW is relatively fast, nice approach.

Resources