Reporting on who and how many times an individual has attended events

Greeting all. I am my wits end on this one because I'm pretty sure this can be done but I'm not quite finding out how. The problem I'm trying to solve is as follows:

We manage multiple events in Smartsheet. We use forms for sign ups so all the data about who is attending our events is in multiple sheets.

Using email address as a unique identifier (which I'm open to changing if you have a better idea), we'd like to get a census of how many and which events a person has attended. For example John Smith - 5 events - May 3, May 4, etc.

We have well over 50 sheets with sign up information. Reports have been a good way to get a sense of things but I can't write formulas off reports.

Any thoughts?

-Melissa

Find more posts tagged with

Forms

Sheets

Formulas

Reports

functionality

Accepted answers

All comments

Andrée Starå

Hi Melissa,

How many different people can there be?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Melissa P-B

Hi Andree,

The list of total attends is over 1,000 but I suspect that we have about 300 people who keep coming back for more than one event. I want to have an inventory of unique attendees and a sense of who they are and how many times they are signing up.

Does that help?

-Melissa

Andrée Starå

Ok.

If you can have a list in another sheet of all the people and add to that list so it's current we could use cross-sheet formulas to see their attendance.

Would that work?

Paul Newcome

I feel like having all of your forms populate the same sheet and then working from there would be much easier. You could use Copy Row Automations and reports to spread the data out for the overall views of each event, but all of your main data would be on one sheet which would make cross sheet references for metrics much easier instead of having to reference 50+ sheets.

Melissa P-B

Paul - I wish I we could do that - we have so many different courses and the management of the attendance and the form is already very distributed. I do think, however that is a potential solution going forward! I'm wondering if a VLOOKUP or Index Match formula might be the answer. I''m still researching.

Thank you so much for the recommendation - I wish I had thought of that in the beginning -much more elegant.

-Melissa

Paul Newcome

There may be some other options.

I am thinking something along the lines of a JOIN(DISTINCT to pull a single entry of every email address on the sheet. You could then parse that down a hidden helper column which would allow you to run your counts. In another helper column you could join email and count together into a string then collect all of those email/count strings into a single long string.

Use cell linking to pull this to a master metrics sheet where you can parse it out to have a list of distinct emails and their counts.

Do this on each of your event sheets and link those results to your master metrics sheet.

Once you have each of those master strings parsed out, you can use a similar idea where you collect all of the distinct emails and use a SUMIFS to grab your totals.

It is a little daunting in the beginning trying to get it all set up for 50+ sheets, but once you have run through it a few times, the remaining sheets should go pretty quickly. After that it shouldn't be too terrible to set it up as needed as new event sheets are added to the portfolio.

I think there may be an easier way on each sheet to pull the data too. If you add in a helper column that simply replicates the row number, then another helper to basically count out distinct email address from the top, you could use a SMALL or LARGE function to pull the distinct entries into a listing. Then do you count column and your combine column to pull to the master metrics sheet.

Does all of that rambling make sense? I should be able to put together an example over the next day or two.

Melissa P-B

Thank you Paul - this is very helpful.

I just had a prodesk session and the idea we had was to create a metric sheet of all the emails and then do a countif(sheet, @row) for each one for each sheet and that summarizes each sheet in columns on the one metric sheet.

In preparation for that, I ran a report of all emails across the 6 main sheets (to see if it was manageable) and discovered that I have 8,250 email entries. In order for our plan to work, I'd have to clean up the list of emails to establish the list of emails without duplicates and give the volume, I'm not sure that's the way forward. I'd also need to repeat the process every so often to catch new email addresses.

So - I'm definitely interested in both the Join(Distinct) idea and the Small/Large idea. The individual sheets don't have duplicate entries so it's really about looking across the 6 big sheets (for now) and counting. Once I can capture the list of unique email addresses the rest is pretty straightforward (she says confidently)!

Any examples would be helpful!

Paul Newcome

Hmm... That changes my idea up a bit. What is the max expected number of entries on a single sheet?

Melissa P-B

The largest sheet I have has 2,800 entries but I'm thinking of starting a new sheet instead of adding to that one so I think I can safely say that the max would be 3,000.

Thanks Paul!

Paul Newcome

Ok. And if you compare the first letter/number of each of the email addresses... Would you say they are pretty evenly dispersed across the alphabet/numbers generally speaking?

The reason I ask is that Smartsheet can only handle up to 4,000 characters including spaces within a string. that means we are going to have to find a way to collect the email address in sections to be able to pull, parse, then evaluate them.

Melissa P-B

Wow - that sounds... complex. At a glance I'd say there are some letters that have more representation than others - more 'j' than 'l' for example.

I'm considering just pulling every email into a sheet (copy/paste from the report 500 at a time) and manually deleting duplicates but every time I consider it... I decide against it.

Paul Newcome

That actually gave me another idea. I wonder if you can do a dynamic push from a report into an excel spreadsheet. I know you can with regular sheets, but I never tried with a report. If that is possible, then excel has built in tools that really help clean up dups. Do the calculations and whatnot in excel then (on a regular basis) manually pull that data back into Smartsheet for reporting.

If the dynamic push of a report does work... Would that be an option?

Melissa P-B

That would absolutely work - I'd have to learn the Excel stuff but if the tools exists, I'm all in.

Paul Newcome

Ok. It might take me a few days to set up and test (work is busy), but I will definitely keep you posted as I figure things out and test them.

Melissa P-B

Ok - I'll play around too ... thank you Paul, very very helpful!! Stay safe.

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.