Any to any comparison in excel

samery Are you using Excel for MS365? If so, here's a couple of recursive formulas you could try. However, they will only return the most optimized combination of vendors...

To return a summary of the optimal combination of vendors with their total items covered, try the following formula in any cell (with nothing below it):

=LET(
F, LAMBDA(X,head,data,val, LET(
   a, BYCOL(data, LAMBDA(col, SUM(col))),
   b, MAX(a),
   c, XMATCH(b, a),
   IF(b=0, val, X(X, head, FILTER(data, INDEX(data,, c)=0, 0),
      VSTACK(val, HSTACK(INDEX(head, c), b)))))),
F(F, C1:Q1, C2:Q231, {"Company","Total Covered"}))

With your current data, the results should look something like this:

To return a detailed list of the optimal combination of vendors with all of their items covered, try the following formula in any cell (will nothing below it):

=LET(
F, LAMBDA(X,head,data,val, LET(
   a, BYCOL(data, LAMBDA(col, SUM(col))),
   b, MAX(a),
   c, XMATCH(b, a),
   d, INDEX(head, c),
   IF(b=0, val, X(X, head, FILTER(data, INDEX(data,, c)=0, 0),
      VSTACK(val, IFNA(HSTACK(d, FILTER(TAKE(data,, 1), INDEX(data,, c)=1)), d)))))),
F(F, B1:Q1, B2:Q231, {"Company","Items Covered"}))

Finally, to return a list of all items not covered by any vendor, try the following formula in any cell (with nothing below it):

=LET(
data, B2:Q231,
qty, BYROW(data, LAMBDA(r, SUM(r))),
VSTACK("Items Not Covered", FILTER(TAKE(data,, 1), qty=0, "none")))

It's a start, anyways. I'll think about it some more and try to find a way to generate other combinations of vendors.