Display Select Few Rows from a Group

Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Camille ✭✭
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Camille ✭✭
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @Camille

    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.

  • randolphj091
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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