Display Select Few Rows from a Group
I have a report, in which I grouped according to project. Each project has several entries/rows showing updates for each project. On my Dashboard, I only want to show the first 4 entries for each project. Is there a way I can do this without relying on the date that the entry was entered?
Best Answer
-
Hi @Camille
There may be a more succinct way to do this, but what I would do is add a helper column and use the LARGE function to evaluate each row see if the Date in that row is either equal to the 1st, 2nd, 3rd, or 4th most recent date. If it is, return 1, 2, 3, or 4. Then you can use this as a Filter in your Report.
For example, this would find the Largest date:
=IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 1), 1,
The COLLECT function filters this per-project, so you'll have 1 - 4 returned for each unique Project name.
Try this full formula:
=IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 1), 1, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 2), 2, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 3), 3, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 4), 4, ""))))
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Camille
I hope you're well and safe!
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots of the sheet? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Camille
We could potentially add a helper column in the source sheet(s) to identify the top 4 rows per-project category. Then we can filter by this helper column in the Report. It's likely that the formula would need to either reference a date column or an auto-number system column though.
I agree that it would be helpful to see screen captures, but please block out sensitive data!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Andrée Starå and @Genevieve P. ,
My sheet looks something like this. For each project, the corresponding team lead provides issues/updates on the project. Some people update on a regular basis (i.e. weekly, biweekly, monthly), while others update sporadically.
Then, I generated a report which looks like this, where I grouped according to project. I want to display this report on a Dashboard, but I only want the first 4 entries for each group showing. For example, I only want updates 16-19 showing for Project 1, Updates 5-8 for Project 2, and so on. Is there a way to do this? Thanks!
-
Hi @Camille
There may be a more succinct way to do this, but what I would do is add a helper column and use the LARGE function to evaluate each row see if the Date in that row is either equal to the 1st, 2nd, 3rd, or 4th most recent date. If it is, return 1, 2, 3, or 4. Then you can use this as a Filter in your Report.
For example, this would find the Largest date:
=IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 1), 1,
The COLLECT function filters this per-project, so you'll have 1 - 4 returned for each unique Project name.
Try this full formula:
=IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 1), 1, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 2), 2, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 3), 3, IF(Date@row = LARGE(COLLECT(Date:Date, Project:Project, Project@row), 4), 4, ""))))
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. This formula worked. Thank you so much !
-
I'm glad to hear that! Thanks for letting me know. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Excellent!
Happy to help!
I saw that Genevieve answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I am receiving a syntax issue when I try this formula. Can you see what I am missing. Data layed out just like your example above Genevieve, however my column name is Location Name instead of project.
=IF(Date@row = LARGE(COLLECT(Date:Date, Location Name:Location Name, Location Name@row), 1), 1, IF(Date@row = LARGE(COLLECT(Date:Date, Location Name:Location Name, Location Name@row), 2), 2, IF(Date@row = LARGE(COLLECT(Date:Date, Location Name:Location Name, Location Name@row), 3), 3, IF(Date@row = LARGE(COLLECT(Date:Date, Location Name:Location Name, Location Name@row, 4), 4, "")))))
See anything obvious?
-
Two things here! 🙂
1) Any time your column name has a space in it (or a number), you'll need to wrap it in square brackets, like so:
[Location Name]
See: Create a Cell or Column Reference in a Formula
2) I notice you have 5 closing parentheses at the end: )))))
You only need 4 - one for each IF!
Try:
=IF(Date@row = LARGE(COLLECT(Date:Date, [Location Name]:[Location Name], [Location Name]@row), 1), 1, IF(Date@row = LARGE(COLLECT(Date:Date, [Location Name]:[Location Name], [Location Name]@row), 2), 2, IF(Date@row = LARGE(COLLECT(Date:Date,[Location Name]:[Location Name],[Location Name]@row), 3), 3, IF(Date@row = LARGE(COLLECT(Date:Date, [Location Name]:[Location Name], [Location Name]@row, 4), 4, ""))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives