Calculate sums based on values

[jbcdfw]

hey guys - this is a tough one and I am in DESPERATE need of your Excel Guru Greatness!!!

I have two spreadsheets - one has leads - one has our ICP criteria on it.

The ICP has like 10 questions with say 4 possible answers per question. Each answer has a "score value" so the answers are rated.

I need to compare the first spreadsheet and the second spreadsheet, tally the score values - and push said values to a new Excel doc with the company name, their vertical, contact info, and overall score.

for example -
Spreadsheet A may have Persons Name, Job Title, Phone, Company, Industry, Email, Earnings, Locations, and Num of Employees.
Spreadsheet B has industry, earings, locations, num of employees - each field has a value based on option - for instance, industry may have Commercial, Residential, Industrial, and Technology - those four have a value assigned to them - Commercial - 10, Residential - 0, Industrial - 20, Technology - 30.

When we compare, based on that above info - on the new spreadsheet, any contact from spreadsheet A that has an Industry of Commercial, will get assigned a value of 10.

Then let's say Num of Employees has values of 1-50 employees - 10, 50-100 employees - 20, 100-200 employees 30. So, any contact on spreadsheet A that has a Num of Employees of 50-100 is now assigned a value of 20.

That value gets added to the value from the industry and in this case, since it was commercial, we add 10 + 20 so that company now has a 30 score.

Am I making sense?

My thought was we have Spreadsheet A (its own file) and Spreadsheet B (its own file) and the "results spreadsheet" is its own file, and it looks for "Spreadsheet A and B" as defined in the script to execute the calculations and then creates a new tab in called Results that has the general contact info of the company and their total score at the end.

So to recap - I need to take data from spreadsheet A compare it to data in spreadsheet B and based on those answers parse out a new document with general contact info a total score. PLEASE let me know if you have any questions I have to make this happen and while I am not an excel noob - this is a bit of a stretch for my skillset.

 

[NateSC]

You can certainly write a macro to do this or use PowerQuery. The latter is faster to pull off, though not quite as intuitive. With a macro, you read in a value in Spreadsheet A, go look up the value in Spreadsheet B, and read the result from B, and then copy from both sheets over to the Results sheet. More tedious than hard though you can write some functions or subs that run with different inputs or ranges.

With Power Query, I would recommend you search the internet for "Power Query: Lookup value in another table with merge Dec 16,2022". I used Google, fyi. The article from that date (ExcelOffTheGrid) walks you through the Power Query setup for this type of situation. The Location and Industry lookups are straightforward LeftJoins. The Number of Employees and Earnings are more complicated. Follow the steps carefully. I did have to change the "Sort" on those steps to be descending instead of ascending so there are a couple of tricks, but it isn't too bad. Also, if you have customer data that does not match one of the tables, it will return a null (or blank) for that field. Once you have it running, you update Spreadsheet A or B and then in your Results spreadsheet you hit Data-->"Refresh All" and it will very quickly update.

I recommend setting up your information in Spreadsheet B as tables either way. It makes it much easier to reference in a macro or pull into the PowerQuery data model. Plus, if you add lines to the table, you don't need to go change references to accommodate the new rows.

If you don't have it, you will want data validation on Spreadsheet A to ensure the inputs for Industry and Location match what is in your tables and values for Earnings and Number of Employeees are actually numbers.

Good luck!
Here is a quick glance at my 3 spreadsheets - I'm not sure how or if XL2BB would capture the PowerQuery stuff:

 

[jbcdfw]

@NateSC Thank you soooooo much for the detailed response. I will give this a go later this week or next week and follow up. I can't wait to give it a try. Thank you again so much! I will keep you posted.

 

[NateSC]

Here is a quick glance at the PowerQuery side. I added the badly drawn red lines and labels to give a brief description of what each section does.