Max Row Within a Group

Sandi J

Hello,

I have sheet that gets a row added every time someone add a response. The example shown is a simplified version.

Each response is associated with a Group of combined columns (1 company + 1 ticket) - See column named "Join". Users can add a new response for each Group over and over. Each time they add a row, the RowID increases automatically.

I want either a report or a sheet that displays the most recent response for each Group (Company + Ticket. An example of the result is below:

Rows 019, 024, and 026 would not be included because a new row overwrote them.

Details to note:

1. I do not have the Dynamic View add-in that allow someone to edit a field. To be clear, I have no add-ins.
2. The user adds and updates from a form. No request for update will be sent.
3. I couldn't use Max Date because I am not able to add a date column with a time stamp; however, I don't know if that is even relevant in this case. The problem is finding the max for a group of rows associated with Join.

Has anyone else run into this issue and found a solution?

Thank you for your help!

KPH

Hi @Sandi J

I would use the Row ID that you already have to identify the latest, as you say "Each time they add a row, the RowID increases automatically." The Row ID is currently text which prevents any math being done, but if you are willing to include another hidden column to convert this to a number, you can use the MAX of that number to find the latest row.

The column to hold the numerical Row ID would have this formula in it

=VALUE([Row ID]@row)

This can be converted to a column formula to ensure it is never missed out.

Now, you can use this ID in your formula to find the latest result. There are a few formula that will get the same result. I think this is the easiest to explain:

=INDEX(Response:Response, MATCH(MAX(COLLECT([Row ID as Number]:[Row ID as Number], Company:Company, Company@row, Ticket:Ticket, Ticket@row)), [Row ID as Number]:[Row ID as Number], 0))

Here is how it works:

=INDEX(Response:Response,

means it will return a value from the Response column

MATCH

means the row it returns is the one that matches something

MAX

means the thing it will match will be the biggest number in the set of numbers coming up

COLLECT([Row ID as Number]:[Row ID as Number], Company:Company, Company@row, Ticket:Ticket, Ticket@row)

defines the set of numbers to be the values in the Row ID as Number column where the value in the Company column matches the value in the Company column in the current row and the value in the Ticket column matches the value in the Ticket column in the current row

[Row ID as Number]:[Row ID as Number]

is the range it will look in for the match

And the 0 at the end

means this must be an exact match.

So it will return the Response from the row with the row with the highest Row ID where the Company and Ticket match those in the current row.

It does not use the Join column.

I hope that does what you need.

Sandi J

Thank you! I am working through it on my test sheet. I want to make sure I understand. Please let me know if I misinterpreted:

This nested formula is placed in each column I want brought back to the sheet with the

INDEX(Response:Response... changed to Company:Company and Ticket:Ticket to gather the data in that column.

I will update as soon as I work it through. Much appreciated.

KPH

The INDEX MATCH formula goes in one column on every row. In my example, it is in the column called Most Recent Response. This gives you the most recent (based on row id) response for that row's combination of Company and Ticket.

Sandi J

Thank you. I am still struggling to get it to work and will update again tomorrow.

The second sheet should only include 1 line for each distinct combination of company and ticket with a corresponding column for the max response on each row. If I were writing in SQL, I'd have it done by now. 😕

KPH

If you want to do this in a separate sheet and only include one of each Company/Ticket pair then you can set up a new sheet with the Company and Ticket columns in it and use an INDEX MATCH to pull the Most Recent Response from the first sheet (you can then hide the column in the first sheet).

=INDEX(COLLECT({Most Recent Response}, {Company}, Company@row, {Ticket}, Ticket@row), 1)

This will return:

If you use an IFERROR function you can remove the error messages where there is no match.

=IFERROR(INDEX(COLLECT({Most Recent Response}, {Company}, Company@row, {Ticket}, Ticket@row), 1), "")

Sandi J

Thank you! I wonder if I may be approaching this thing from the wrong angle. I tried to simplify it for illustration, but that may have caused me to focus on it correctly. Here's the details without simplification.

I'm putting together a list of Use Cases (ticket) that our member companies (Company) will update with the type of solution (Response) they used to solve the Use Case.

Example use cases: Use Cases are problems the company is trying to solve.

• Employee Self-Service Chat Box
• Custer Survey Sentiment
• Revenue Forecasting Model

Example Solutions: Solutions are the technology approach used to solve the problem.

• Homegrown (The member built the solution themselves.)
• Commercial (The member bought the solution.)
• Backlog (The member has not started the project yet, but it is on their roadmap.)

How the list will be used:

• Members can add a use case so other members can see what they need help with or let other members know they have a solution. They can add a use case with Backlog as the solution let others know they haven't started the project yet or one of the other options to indicate how they solved the problem in case anyone wants to contact them.
• Members can add their solution for a use case already listed.
• Members can change their solution for an existing use case from their original solution to a new solution. For example, they may have added a use case with "backlog" but need to update it with "homegrown" because they built it.
• Members will need to use a form to add or update to be sure they don't accidentally change someone else's information.
• Member Company names and existing Use Cases will be on a dropdown. I'll have to manually add new Use Cases to the dropdown.
• We'll use this information to identify the most "popular" use cases then creating working groups to solve them.
• In addition, members can see which other members have solved a use case and reach out to them about it.

I've been setting up a mini database to pull all this information together into one sheet. I am open to other ideas. I don't think this would be an issue if I had Dynamic View, but I don't have that licensing.

KPH

I’ll read this properly tomorrow, but in the meantime, have you looked into Reports? They can be pretty powerful for analyzing data in sheets and are very simple to create.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

Sandi J

Not really, but I will take a deep dive into reports tomorrow. Thank you!

Sandi J

Thank you for the heads up about report. I see reports can be edited and that they can combine sheets (which is helpful). However, I am still having the same issue. The people reviewing the reports and updating data won't have access to the underlying sheet because they don't have a Smartsheet account. They have to use a form.

The problem still lies in getting the max row, so I am back to solving that issue. I'll use your examples. I still can't help but think I need a distinct list populated on a new sheet to be filled in with the max row number THEN filling in the data from the row using the max row # as the key field.

KPH

The reports should solve the issue of viewing the results: things like identifying the most requested use cases. Reports can be published and viewed by those without smartsheet accounts.

You will still need sheets to hold the data and forms to collect the data.

---

The problem with creating a sheet to work like a report, is that you need something on the page to start with. In the first example I added the most recent response as a column next to data that already existed - that data was used to determine which value to return. In the second example, I entered all the company/ticket combinations and used that to determine which value to return.

If you want to start with a clean sheet then you do need a formula to create the list of company/tickets and then the formula to bring in the value based on that column. You can use an INDEX DISTINCT formula (and I would do that on the JOIN column you had originally). You will also need a row number column to iterate the distinct result to return. Then you can adapt the INDEX COLLECT to use the joined column rather than the two separate columns

=INDEX(COLLECT({Most Recent Response}, {Company Ticket}, CompanyTicket@row, 1)

Does that make sense?

Sandi J

Yes. That makes sense. I am trying get it to all work together. You are so very helpful. Thank you! I will update you with the results.