How to Phrase a Question for the AI or Write the Formula for Myself for Grouping Data

Hello,

I would like to see if anyone can help me parse entries across different columns.

What I have in my SS is a list of registration numbers in Column A and departmental associations in Column B. I would like to count how many different registration numbers appear in Column A and then group them by their departments in Column B. Multiple entries can have the same registration number. The data of several hundred entries looks like this:

Registration #	Department
HA3405	CC
HA3405	CC
HA1123	CC
HP3687	MH
HP3687	MH
HG5312	MH
HG5312	MH
HG5212	MH
NB5551	MH

So how would I get SS to report that there are 2 different registration numbers associated with department CC and 3 (ETA: 4) different registration numbers associated with MH, regardless of the number of individual entries?

Thanks for any help!

Find more posts tagged with

Formulas

functionality

Automations

Accepted answers

Akblevins

Hi @Smaug (love the name btw!)

so here's what I ended up asking Smartsheet. You would need these in maybe a metrics sheet or even sheet summary level - one for each department.

And in your data "MH" actually has 4 unique Registration Numbers not 3 😊

I do find AI very helpful with formulas like these, the more intricate the formula, the less likely AI is going to help you build all of it.

All comments

Akblevins

Hi @Smaug (love the name btw!)

so here's what I ended up asking Smartsheet. You would need these in maybe a metrics sheet or even sheet summary level - one for each department.

And in your data "MH" actually has 4 unique Registration Numbers not 3 😊

I do find AI very helpful with formulas like these, the more intricate the formula, the less likely AI is going to help you build all of it.

Smaug

Thanks so much, that worked like a charm! It looks like I don't have a good handle on the verbiage I need to use for the automation yet.

Jgorsich

The suggested formula will give you the counts, though you will have to manually enter the department abbreviation you are looking for. You also mentioned wanting to group the data - do you need that or is just getting the count sufficient? Note - if you don't want to manually edit the department entry, you can make a list of department codes in a column called "DepartmentCodes" and modify the formula to be:

=count(distinct(collect([Registration #]:[Registration #], department:department,DepartmentCodes@row)))

and then just fill the formula down for the full list of codes.

If you want the data grouped as well, a bit more work is needed, including a column of integers to use as an index pointer.

Smaug

Thanks so much!

Re: grouping data, I was thinking that would be useful for creating charts to hand my coworkers. The raw data is probably going to be less desirable for someone to look at in a presentation.

Akblevins

Trust me, it takes some time 😊

If you haven't yet I do recommend looking into Prompt writing videos or trainings. I've had to take a few for my job as I work in IT and it has helped immensely. Also, what @Jgorsich said below also works if you go the metric sheet route (and much cleaner as it is just 1 formula for as many rows as you need). I split mine out in my response in case you use sheet summary level reporting.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.