0

I would like column C to be column A with everything in column B filtered out. Column A may have duplicates and those should carry over into column C, unless the same number of that item appears in column B, as pictured. Is there a function able to do this, or would I need to use an app script to accomplish this? column C is generated from the column A and B inputs

1

2 Answers 2

1

The formula returns values from a column after first removing blanks and any values matched one-to-one in another column.

For example, if there are 5 instances of the cell value alpha in column A, and 2 instances of alpha in column B, then 3 unmatched instances will be returned because alpha instances 3, 4, & 5 from column A are unmatched while instances 1 & 2 are matched in B.

Formula

=LET(rangeA, A:A, rangeB, B:B, 
   _a, SORT(TOCOL(rangeA,1)),
   _b, TOCOL(rangeB,1),
   calc, LAMBDA(x, TOCOL(BYROW(UNIQUE(x), 
     LAMBDA(ux, LET(fx, FILTER(x, x=ux), 
       TOROW(ARRAYFORMULA(fx & 
       SEQUENCE(ROWS(fx))))))),1)),
   FILTER(_a, NOT(COUNTIF(calc(_b), calc(_a)))))

Explanation

  1. rangeA stores the initial column of values.
  2. rangeB stores the column to check for duplicates.
  3. _a is a sorted array from rangeA with the blanks removed.
  4. _b is an array from rangeB with the blanks removed.
  5. calc stores a LAMBDA formula that is used to convert any column of values that may include duplicates into a column of unique values. This formula is stored as a LAMBDA so it can be reused which eliminates unnecessary code duplication.
    1. The values are made unique by appending (joining) a number to each value using the following logic:
      1. All unique values have 1 appended to them. eg. If alpha and charlie are unique values, then alpha1 and charlie1 are returned.
      2. For duplicate values, a sequence of numbers is appended starting from 1. eg. If bravo appears three times, then bravo1, bravo2, bravo3 would be returned.
    2. The LAMBDA stores the column it is passed in the variable x.
    3. The LAMBDA's formula is a BYROW function that uses UNIQUE to remove any duplicates from x before passing them one-by-one into another LAMBDA function which stores the current value in ux. For each ux, that LAMBDA's formula:
      1. FILTERs x for any values that match ux and stores the result in fx.
      2. Creates an array that's a number SEQUENCE from 1 to the total number of values in fx
      3. Appends the number sequence to the array fx inside an ARRAYFORMULA that is itself inside a TOROW function.
      4. The process is repeated for each x until none remain.
      5. The resulting array of rows is converted to a column.
    4. So, if x is
      {"alpha";"bravo";"bravo";"bravo";"charlie"}
      Then ux is
      {"alpha";"bravo";"charlie"}
      And calc returns
      {"alpha1";"bravo1";"bravo2";"bravo3";"charlie1"}
  6. The last step is to FILTER _a using a COUNTIF on calc(_b) and calc(_a)
1

Slightly different approach:

=arrayformula(let(
source,A:A,
remove,B:B,
runcountcat_,lambda(x,
   map(index(x,1):index(x,counta(x)),lambda(each,each&"~"&countif(index(x,1):each,each)))),
regexextract(unique({runcountcat_(source);runcountcat_(remove)},,1),"(.+)~")))

Append the running count of entries from both the 'source' and 'remove' lists to each entry using map, then use the exactly once argument of unique on the combined list to leave the remaining entries in the source list, and regexextract to eliminate the appended count.

1
  • Thanks nice approach.
    – Blindspots
    Mar 13 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.