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

Paul Newcome

Apologies for the delayed responses. Busy busy.

You would use the ranking system in your source sheet, but you would use a COLLECT function in the second portion of the RANKAVG function to grab the Average Review range based on rows that have a matching location.

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

Then in your metrics sheet you would need three helper columns. One for each of the three highest ranks. You would use a LARGE/COLLECT combo to pull in the 1st - 3rd largest ranks on from the source sheet:

=LARGE(COLLECT({Source Sheet Rank Column}, {Source Sheet Location Column}, @cell = Location@row, {Source Sheet Category Column}, HAS(@cell, Primary@row)), 1)

Change the 1 at the end to a 2 and then a 3 for the second and third largest. This would go in every row on your metrics sheet.

Then to populate the vendors, you would use a JOIN/COLLECT combo along the lines of:

=JOIN(COLLECT({Source Sheet Vendor Column}, {Source Sheet Rank Column}, @cell = [1st Rank]@row, {Source Sheet Location Column}, @cell = Location@row, {Source Sheet Category Column}, HAS(@cell, Primary@row)), ", ")

Erik D

@Paul Newcome, could RANKEQ(COLLECT()) or RANKAVG(COLLECT()) solve this for me?

Paul Newcome

Did you update the RANKAVG function in the source sheet to include the COLLECT function as indicated at the top of my last post?

Erik D

@Paul Newcome, no worries on the delay. Same here, I've been out of pocket traveling for work.

Thank you so much for your reply. I implemented what you suggested with some minor changes (due to our Approved Locations column being multiselect for include multiple locations):

On the Vendor Master List sheet, I made three rank columns (one for each location)

=IFERROR(RANKAVG([Average Review]@row, COLLECT([Average Review]:[Average Review], [Approved Locations (Internal)]:[Approved Locations (Internal)], HAS(@cell, [Location C Rank]@row)), 0), "Not Approved")

On the Vendor Reviews Metrics sheet, I did as you suggested with three helper and reporting columns:

Helper First column formula: =IFERROR(SMALL(COLLECT({Vendor Master List - Location A Rank}, {Vendor Master List - Approved Locations}, HAS(@cell, $Location@row), {Vendor Master List - Type}, HAS(@cell, $Primary@row)), 1), "")

Location First column formula: =JOIN(COLLECT({Vendor Master List - Company Name}, {Vendor Master List - Location A Rank}, @cell = [Helper First]@row, {Vendor Master List - Approved Locations}, HAS(@cell, Location@row), {Vendor Master List - Type}, HAS(@cell, Primary@row)), ", ")

Ideally I wouldn't have those extra three columns on the Vendor Master List, but it seems to be working. Next step is to get this on a dashboard, but I should be able to figure that out. Thank you so much for your help!

Erik D

@Paul Newcome, I added some additional test reviews to test the new functions.

• After adding the "Carson" entry which is only approved for Location A, it's showing up as a top ranked vendor for Location B.
• Additionally Location A Rank is correctly averaging the tied review average to yield 1.5; however, for Location B (where Carson shouldn't be showing up at all) it's not averaging the top scores and showing both Atlas Po- and Carson as #1s

I've tried to run additional tests (e.g. adding additional reviews for Carson) which seems to fix the problem. I'm just not sure if this is a Smartsheet refresh issue or something wrong with the formulas.