Requesting help reporting top three vendors for three locations based on a common review database

Erik D

Our team is trying to build a tool to intake vendor reviews based on five averaged criteria to inform vendor selection on future projects. I've built the form, a review collection database, a review metrics helper sheet, a vendor masterlist and a preliminary report.

I'd like to be able to consistently report the top three vendors for each of our three locations by pulling data from our Vendor Master List to our Review Metrics Helper Sheet to allow displaying on our Vendor Review Report/Dashboard.

{Vendor - Review Average}, {Vendor - Location}, and {Vendor - Type}

This is the formula I'm using:

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1), {Vendor Master List - Location}, IF(HAS({Vendor Master List - Location}, "Location A", "Location A"), {Vendor Master List - Type}, IF(HAS({Vendor Master List - Type}, "Type A", "Type A"))), 1)

The formula is returning an #INVALID VALUE error. Potential sources of error:

• The {Vendor Master List - Location} and {Vendor Master List - Type} ranges can contain multi-select criteria (e.g. Vendor B provides Type A and Type C services to Location B and Location C)
• Syntax error with my formulas
• Using incorrect formulas for the task

Any help is much appreciated!

Hollie Green

You closed out the collect part of the formula where I have highlighted remove the ,1) and try it.

Paul Newcome

@Hollie Green That is closing out the LARGE function.

@Erik D Are you able to provide some screenshots for context as well as explain the logic for using the IF statements for the criteria?

Hollie Green

Thanks @Paul Newcome Missed that so stopped looking further but unless I am overlooking closing parenthesis the first HAS function is not closed out.

Paul Newcome

@Hollie Green It looks like neither of the HAS functions are closed out.

Erik D

@Paul Newcome, Thank you for the reply. Screenshots below:

Vendor Reviews sheet:

• New entries are created with a form.

The reviews are combined in the Vendor Master List sheet:

• Reviews are pulled from the Vendor Reviews sheet using this formula: =IFERROR(AVG(COLLECT({Vendor Reviews - On-time Delivery}, {Vendor Reviews - Company Name}, [Company Name]@row)), "No Reviews")
• Reviews are averaged using: =IFERROR(AVG([On-time Delivery]@row:[Customer Satisfaction]@row), "Missing Data")

• Vendor Master List also contains columns to qualify location and type

My goal is to report the top three vendors according to vendor location and subtype. I'm using the IF(HAS( formula to return the top three averaged review score vendors for a certain location and a certain subtype. Like I said in the original post, the IF/HAS combination might not be the most efficient way of Collecting a specific vendor subtype.

Erik D

@Hollie Green and @Paul Newcome, I'll check the rest of my parentheses. Thank you.

I also came across this old question that seems to related to a similar problem: https://community.smartsheet.com/discussion/79316/using-if-functions-has-contains-etc-and-or-for-searched-fields

Hollie Green

I believe the below should work to pull in the company name with the largest average that also has location A and Type A. I do miss Parenthesis at times so may need to double check those if it doesn't work.

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1), {Vendor Master List - Location},Has(@cell, "Location A", {Vendor Master List - Type},{Vendor Master List - Type},Has(@cell,"Type A")), 1)

Erik D

@Hollie Green, thank you. It looks like there is an extra {Vendor Master List - Type}, so I modified it as follows:

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1), {Vendor Master List - Location}, HAS(@cell, $[Primary Column]$104), {Vendor Master List - Type}, HAS(@cell, $[Primary Column]@row)), 1)

where $[Primary Column]$104 is Location A and $[Primary Column]@row is Type A.

However, with Location and Type reference cells AND "Location A" and "Type A" manually entered into the formula. It's returning an #INVALID VALUE error. There is missing data in our reviews; however, even for location, type, and reviews with complete data it's still returning the #INVALID VALUE error.

Erik D

I'm not sure the that the HAS() function on its own can provide the right input to the COLLECT() function. The HAS() function is returning a TRUE value which isn't a valid multi-select in either the {Vendor Master List - Location} or {Vendor Master List - Type} reference columns.

That is why I nested the HAS() in an IF() statement; to return the correct Location or Type to the COLLECT() function.

However, even when I remove the HAS() function and manually code the desired Location and Type it still returns #INVALID VALUE:

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1), {Vendor Master List - Location}, "Location A", {Vendor Master List - Type}, "Type A"), 1)

Erik D

@Hollie Green and @Paul Newcome, I figured out the source of the #INVALID VALUE.

I'm getting #INVALID VALUE because the Vendor with the Largest average review is not located at Location A or perform Type A work. I assumed the formula would return the vendor name with the largest review for Location A and Type A work, but it's just returning an error when those criteria don't match.

Maybe this is a better way to frame our goals:

For a given Vendor Location and Vendor Type, how do we return the top three vendors for those criteria?

Erik D

@Hollie Green and @Paul Newcome, I'll just keep updating my progress here. Either I'll figure it out eventually or maybe you'll spot a path forward!

I've managed to eliminate any errors by wrapping the INDEX(COLLECT() function with an IF(AND(HAS())) statement:

=IF(AND(HAS({Vendor Master List - Approved Location}, $[Primary Column]$104), HAS({Vendor Master List - Type}, $[Primary Column]@row)), INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1)), 1), "No Match")

While there are no errors, I'm not getting the right information. That is, it's returning vendors that don't match the assigned Location or relative row Type.

The returned values: Atlas, American, and A&E are the top 3 overall rated vendors; however, Atlas is a Paint vendor in Locations A and B, American is an Other vendor in Locations A and B,and A&E is an Other vendor NOT approved for Location A or B.

I think the issue is that the HAS() functions are checking the entire column for Locations and Vendors which do pop up somewhere in that column, but not necessarily at the same row with the largest average vendor reviews. I figured out that's what's returning the "No Match" as the "No Match" Types in the screenshot above aren't all included in the Master Vendor List sheet. I believe this is why @Hollie Green suggested nesting the HAS() function inside the COLLECT(), which didn't work for me, but I'll try again.

Erik D

@Paul Newcome I'm trying to recreate the same solution you suggested in this thread: https://community.smartsheet.com/discussion/85482/formula-index-collect-match-by-rank

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Type}, Primary@row, {Vendor Master List - Approved Location}, Location@row), {Vendor Master List - Average Review}, @cell = LARGE({Vendor Master List - Average Review}, 1))

I'm guessing the #INVALID DATA TYPE error is from the multiselect dropdown columns in Approved Location and Type, but I'm not sure how to correct it.

I tried rewriting the INDEX() function to put the Average Review criteria inside the COLLECT() function, as follows:

=INDEX(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Type}, HAS({Vendor Master List - Type}, Primary@row), {Vendor Master List - Approved Location}, HAS({Vendor Master List - Approved Location}, Location@row), {Vendor Master List - Average Review}, LARGE({Vendor Master List - Average Review}, 1)), 1)

But it returns an #INVALID VALUE error and I think puts me back to where I was a few comments up.

I might gave gone full circle at this point 🙃

Paul Newcome

How would you handle a tie?

Erik D

Ideally, the tied vendors would display in the same ranked cell separated by commas or one of the tied vendors would displace the lower ranked vendor (i.e. Vendor #1a is first, Vendor #1b gets pushed to second, and Vendor #2 gets pushed to third)

However, I'm not sure how to implement that. Maybe wrapping the COLLECT() function in a RANKEQ()?

Erik D

Right now the best workaround I can find is to create a Vendor Rank column in the Vendor Master Sheet:

=IFERROR(RANKAVG([Average Review]@row, [Average Review]:[Average Review], 0), "Unranked")

Then by manually using filters on the Location and Type columns (e.g. Location A and Mechanical) with an ascending sort on the new Vendor Rank column we can see the top vendors for a given Location and Type.

We'd either have to create filters as needed or to make it less manual we could save all the filter combinations, but it would be upwards of 60 filters to cover all Locations and Types.