Forum Discussion
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:
Company Total Covered S&P 138 Oxford eco0mics 28 ECONDB 20 Kpler 11 blue water 5 alphaliner 2 economist intelligence 2 Sinay 1 Oxford Analytica 1 TDM 1 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.
You are right probably, I am complicating things.
Please redownload the file again and go to cell B235. The idea is to show what every vendor can provide and then the extra variables that the next vendor can provide above and beyond the variables provided by the previous providers. IS this now clear?
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:
| Company | Total Covered |
| S&P | 138 |
| Oxford eco0mics | 28 |
| ECONDB | 20 |
| Kpler | 11 |
| blue water | 5 |
| alphaliner | 2 |
| economist intelligence | 2 |
| Sinay | 1 |
| Oxford Analytica | 1 |
| TDM | 1 |
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.