How to indicate the Top5 from a smartsheet with Case-insensitive criteria?

Hello! This is my second question here. The community was incredibly helpful for the first one! :)

- I want to pull the Top 5 Customers with more active projects from a project tracker (the user inputs project ID and from a data pool, the Customer Name and other info are pulled) to a dashboard chart. So far, I am using a Helper column on the tracker to identify unique values (customer names) with the following formulas.

ROW1: =COUNT(DISTINCT(Customer$1:Customer@row))

All other rows: =IF(COUNT(DISTINCT(Customer$1:Customer@row)) <> COUNT(DISTINCT(Customer$1:Customer1)), COUNT(DISTINCT(Customer$1:Customer@row)))

- In a different sheet, I am Listing all the identified Customers, counting the number of projects per customer, and then ranking them. Then I use a report to sort them and graph the Top 5.

PROJECT COUNT: =COUNTIFS({Tracker Customer Column}, = Customer@row, {Project Probability}, <>"Closed")

CUSTOMER RANK: =IF([Project Count]@row = 0, 0, RANKEQ([Project Count]@row, [Project Count]:[Project Count]) + COUNTIF([Project count]$1:[Project Count]@row, RANKEQ(@cell, [Project Count]:[Project Count]) = RANKEQ([Project Count]@row, [Project Count]:[Project Count])) - 1)

This is working great, but my problem is that in the data pool, one of the customers has their name registered with lower cases in one project, and with upper in the rest, so this happens:

1- The Helper column identifies upper and lower case projects as if they were different customers (case-sensitive)

2- The project count formula is case insensitive

So in my top 5 chart, I have one customer count repeated.

I want to fix this with one of the following options:

a) Make the helper column case-insensitive. (Preferred)
b) Make the count per customer case-sentsitive. (Accepted)

I have not managed to do any of them. Any thoughts?

I attack images to clarify. The problem is highlighted in red.

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Dashboards

Automations

Project Management

Accepted answers

Jeff Reisman

To add to Paul's great (as usual) advice:

Add another hidden helper column in the source sheet that converts the data from the Customer column into all uppercase:

=UPPER(Customer@row)

Then use that helper column instead of the Customer column to select your distinct, get your counts, and do your ranking.

This way no matter how the user enters the customer name, as long as it's spelled correctly you'll have the same uppercase value to work off of automatically.

All comments

Paul Newcome

Are they all actually entered as "CUSTOMER##" or is that just sample data for the screenshot with actual names being used?

Fernando Ced

@Paul Newcome All data on the picture is sample for screenshot.

The only input is the Project ID tho, the rest of the information (Actual names, costumers, etc) is pulled from a data pool that belongs to another team (which is why I can't change the case of the Costumer registered there).

Paul Newcome

My first suggestion would be to reach out to the other team to ensure consistency.

Aside from that, maybe try working a LOWER function into the formula you use to flag the distinct entries.

Jeff Reisman

To add to Paul's great (as usual) advice:

Add another hidden helper column in the source sheet that converts the data from the Customer column into all uppercase:

=UPPER(Customer@row)

Then use that helper column instead of the Customer column to select your distinct, get your counts, and do your ranking.

This way no matter how the user enters the customer name, as long as it's spelled correctly you'll have the same uppercase value to work off of automatically.

Fernando Ced

@Paul Newcome and @Jeff Reisman Such a simple solution, and I completely missed trying that before!

I ended up adding the UPPER function directly at the beginning of the formula on the "Project Name" column, so that I didn't have any other helper column! Thanks for your help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.