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,
have not had a chance to go through this yet, did have a couple of thought , using

'items on list' as delimiters in text split, also using

Hstack to stack name and item then each Vstacked on top of each other,
I had a go manually, and thought of putting inside REDUCE , but my head started hurting.

Thanks for the time you have taken over this, will get back to you, but my be a little while.

Richard.

Answer 4

Thanks,
I've not had a chance to work through either, being trying other things,
also dull but have to be done things,

Will get around to it, and as always thank you for your help,

Richard.