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.
really hope to get your help so let's change the question differently.
based on the attached excel sheet, let's see I want to create one table for every vendor separately. It goes something like this:
Vendor 1 has 50 variables => this can be done via filter function of excel.
Vendor 2 has 40 variables on top of vendor 1 => this should be done again by filter function but must filter OUT variables already included in vendor 1
Vendor 3 offers 10 variables on top of vendor 1 and 2 again by filtering out already covered variables.
etc.. till end of vendor list. ==> this would conclude assessment of vendor 1
2nd separate table for Vendor 2 starts the same process and on an on...
So the first simple question: how do we use a filter function to filter from an array of 100 rows that have values 1 and filtering out already covered rows from previous rounds?
Sorry,I can not get you by now.
If possible,can you upload an expected result workbook.based on the source dataset you have uploaded.
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?
- Thanks for your response.
B235=80
I still can not find out how to calculate this 80.
variables are B234 and A235?
How these two variables came out 80 manually?- thank you so much. let me digest what you wrote.
B235 = 80 then 40 then 0... are all just dummy data just to let you know what I had in mind.
But I think you captured what I am looking for... hopefully when I understand the response.
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.
just a question please:
in this formula:
=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"}))
The result indicates that S&P should be the lead which is obvious then all the other vendors were added very nicely depending on the extra variables that they provide on top of the previous one. This is what the client wants which is great. But it assumes all variables have equal weighting. Client is saying what if vendor X say Factset which has the least number of variables but nonetheless carries better coverage. So client says what if we trigger another scenario by starting not with a top vendor rather any other vendor as the start then align all the other vendors cumulatively till we close as much as possible the remaining variables.
Now from a sorting standpoint, the result is the same but from a variable importance standpoint the client is insisting on having visibility on all scenarios and then he can conclude depending on other factors (such as coverage, weight, refresh rate,.... and many other stuff which are not mentioned to avoid complication).
So may I ask you the following:
How can please revise your formula to rerun the same logic on each and every vendor (clearly in different tables)? in other words, can your formula be reprogrammed to start with a vendor not as the top in terms of number of variables but as a variable in a cell then a formula would work its way towards the other vendors to add remaining vendors?