Excel Create a column of Items Not on a list

Question

Sorry not good title,

Say I have a two column table names and items bought but I want another if Items not bought by each
person on the first 'sales' list, so on power query this could be

Table.Group( Source, "Name",

{{ "DidNotBuy", each

List.RemoveMatchingItems( ITtable [Items] , List.Distinct([Item]) ) }} )

so bringing in product table as list and subtracted items bought , this can then be expanded and pivoted.

in excel have got as far as this Items = All items, the right table has been sorted, but his is just for clarity,

any ideas?

Richard. s

Answer 1

Hi,

My not be a perfect solution but gets most of what you want

=DROP(REDUCE("",TOCOL(DROP(REDUCE("",UNIQUE(C2:C14),LAMBDA(a,i,VSTACK(a,i&":"&TOROW(FILTER($A$2:$A$6,COUNTIFS($D$2:$D$14,$A$2:$A$6,$C$2:$C$14,i)=0,""))))),1),3),LAMBDA(a,i,VSTACK(a,TEXTSPLIT(i,":")))),1)

Hope this helps.

Answer 2

Using just Excel formulas. ChangeTable1 to the name of the first table (the one with the Items column) and Table2 to the name of the second table (the one with the Name and Item columns).

=LET(
  names, UNIQUE(Table2[Name]),
  num_names, COUNTA(names),
  items, Table1[Items],
  num_items, COUNTA(items),
  all_combos, MAKEARRAY(num_names*num_items, 2,
    LAMBDA(r,c,
      IF(c=1,
        INDEX(names, QUOTIENT(r-1, num_items)+1),
        INDEX(items, MOD(r-1, num_items)+1)
      )
    )
  ),
  FILTER(all_combos,
    COUNTIFS(Table2[Name],
      CHOOSECOLS(all_combos, 1), Table2[Item],
      CHOOSECOLS(all_combos, 2)
    )=0,
    ""
  )
)

Answer 3

Hi,

I did say I'd get back, not quite the same as either of you,

= DROP( REDUCE( "",H3:H5, LAMBDA(x,y, VSTACK(x,

IFNA( HSTACK( y, FILTER( Table25[ItemL], ISNA( MATCH( Table25[ItemL], UNIQUE( FILTER( Table36[Item],Table36[Name] = y)),0)))),y)))),1)

Richard.