Display Select Few Rows from a Group

Camille

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?

Genevieve P.

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

Andrée Starå

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!

Genevieve P.

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

Camille

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!

Genevieve P.

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

Camille

@Genevieve P. This formula worked. Thank you so much !

Genevieve P.

I'm glad to hear that! Thanks for letting me know. 🙂

Andrée Starå

@Camille

Excellent!

Happy to help!

I saw that Genevieve answered already!

Let me know if I can help with anything else!

Best,

Andrée 

randolphj091

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?

Genevieve P.

Hi @randolphj091

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