Ignore duplicate rows in a report

Lindsay P.

Hello,

We record certain metrics about our clients anytime they visit our physical location. This usually isn't a problem because they don't come in too often; however, there are a few times a year when they are scheduled to be onsite multiple times a day. This leads to their name showing up repeatedly on the report we use to list each day's visitors, which in turn causes staff to (annoyingly) ask them the same questions every time they check in.

Is there a formula or filter I can use so anyone visiting our building will only have their name show up once a day (regardless of how many times they are scheduled to come in)?

Paul Newcome

You are going to need to use a formula (in another sheet is recommended) to pull the list. Would you want it just for a single day or multiple days within the sheet? How many visitors per day would be the max?

Lindsay P.

Thanks for responding, Paul!

I want to show multiple days in my report, so if someone is visiting our location three times on Monday, four times on Tuesday, and twice on Friday, I want to see their name show up just once for Monday, once for Tuesday, and once for Friday. Make sense?

Paul Newcome

You could group the report by date and then create a sub group by visitor.

Lindsay P.

Hi Paul,

I'm coming back to this question as I had an idea and would love to run it by you. Here is an example of my source sheet:

You'll see I have a column for the attendees of each event and many of them are participating in more than one event for this day. Here is an example of how they show up on the report I created:

We need a condensed list of the attendees for each day and I would love to somehow filter out the duplicate rows so each name only shows up once. I think I can use a column formula (in the sheet helper column "Guest Names") to achieve this with some kind of formula combination using IF, COUNT, DISTINCT, and/or RANK function. The range would be all rows where Level@row = 2 and "Start Date" are the same. I could then use the report filter to only show those rows.

It seems doable to me but I'm inexperienced enough with formulas that I'm struggling with the syntax. What do you think?

Paul Newcome

Try using a sub-grouping in the report.