Forum Discussion
Solved
Any to any comparison in excel
hi Everyone Book1.xls has 213 list of variables on the rows side and on the columns side, you have list of 15 vendors which provide those variables. Client simply would like to know which vendor...
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.
thank you for your kind response.
it's just the table available. Forget about column A, this is just for classification.
Target data columns is the main column containing all 213 variables which my client requires. We asked 15 vendors to see if they have the data and they came back with a yes or no answer which is now reflected on that matrix.
Now client says: It's not enough finding the top match of variables across all 15 vendors. He would like to understand multiple scenarios meaning:
- If I go with first vendor covering let's say 50% of the variables, how many more vendors I require to complete the remaining variables? 2 more? 1 more? 7 more?
- If I go with second vendor.... same question
So the client is saying: present to me a complete analysis so that he can decide not only on coverage and minimum number of vendors but also on other factors which will be added later on such as price maybe, accuracy, and other parameters'.
It could be for example that all variables can be covered by Vendor 7 and Vendor 8 but they happen to be very expensive combined. However going with vendor 3, 9 and 14 could cover all variables too but maybe less expensive and with better feature.
So again client requires multiple scenarios of assessment to make the right conclusion. Hope this helps.
One note I missed in my original post: I think there's 9 or 10 variables that are not covered by any vendors so achieving 100% match is not possible.
and again the result should be a new table looking something like the following:
- Vendor 1 covers 80 variables then combined with Vendor 4 to cover 90 variables...
- Vendor 1 covers 80 variables then combined with vendor 9 covering 50 variables then...
....
- Vendor 2 covering 120 variables with vendor 1 covering 30 variables
In the above example, clearly the function should NOT look for already covered variables of the first vendor in the next vendor and the subsequent ones. For example, if vendor 1 covers tsnuami variables, no need to ask subsequent vendors for tsunami variables, we check with the remaining required variables from the currently full list of variables covered.
it's just the table available. Forget about column A, this is just for classification.
Target data columns is the main column containing all 213 variables which my client requires. We asked 15 vendors to see if they have the data and they came back with a yes or no answer which is now reflected on that matrix.
Now client says: It's not enough finding the top match of variables across all 15 vendors. He would like to understand multiple scenarios meaning:
- If I go with first vendor covering let's say 50% of the variables, how many more vendors I require to complete the remaining variables? 2 more? 1 more? 7 more?
- If I go with second vendor.... same question
So the client is saying: present to me a complete analysis so that he can decide not only on coverage and minimum number of vendors but also on other factors which will be added later on such as price maybe, accuracy, and other parameters'.
It could be for example that all variables can be covered by Vendor 7 and Vendor 8 but they happen to be very expensive combined. However going with vendor 3, 9 and 14 could cover all variables too but maybe less expensive and with better feature.
So again client requires multiple scenarios of assessment to make the right conclusion. Hope this helps.
One note I missed in my original post: I think there's 9 or 10 variables that are not covered by any vendors so achieving 100% match is not possible.
and again the result should be a new table looking something like the following:
- Vendor 1 covers 80 variables then combined with Vendor 4 to cover 90 variables...
- Vendor 1 covers 80 variables then combined with vendor 9 covering 50 variables then...
....
- Vendor 2 covering 120 variables with vendor 1 covering 30 variables
In the above example, clearly the function should NOT look for already covered variables of the first vendor in the next vendor and the subsequent ones. For example, if vendor 1 covers tsnuami variables, no need to ask subsequent vendors for tsunami variables, we check with the remaining required variables from the currently full list of variables covered.
samery Just wondering, is the file you provided complete? There currently are no scenarios where all of the variables are covered. If you filter out all of the 1's, you're left with a list of 21 variables that none of the companies are covering.
- greetings djclements
yes that is correct. At the moment, we noticed that some of the vendors are unable to provide the variables that we wish to have. As such, we are going to have to exclude them for now.
But the table is complete. The list of 200+ rows are the variables on the B column that are actual variables that you can buy from data providers. On the columns side you will find vendors that provide such variables. 1 indicate that the vendor can sell u that variable while 0 means that vendor doesn't have this variable.- Hi everyone
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?- how do we use a filter function to filter from an array of 100 rows that have values 1 and filtering out
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.
