Identify and print 3 most repeated itesm + List of values just once

I'm looking to do this two things:

1- find and print the 3 values most repeated

2- print list of values but just once (if any value is repeated just print once)


I have a form that collects tickets of problems in a given apartment. Each problem is a new ticket. So if there is an apartment with 3 problems i would have 3 tickets.

I already did a formula to count only distinct apartment numbers, but i also want to print the list of apartments that had any problem. And to know which are the top 3 with more problems.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest creating a report and grouping by apartment number.

  • fitome
    fitome ✭✭

    Thank you Paul,

    I think that would be useful for knowing which apartments and which reports they have each. But would be cool to have a list of top 3. As there would be so many apartments to analyze in a report.

    =JOIN(COLLECT({PK AUX Rango 5}; {PK AUX Rango 4}; MAX({PK AUX Rango 4})); ", ")

    With this formula I got to print the apartments with MAX number of reports. Range 4 is a column that counts if duplicate:

    =COUNTIF(COLUMN:COLUMN; COLUMN@ROW)

    Right now I have 2 apartments with 2 reports each. So I could be OK just knowing the apartment with MAX number of reports instead of top 3. The problem is that the first formula is printing each twice, as the second formula is giving me value 2 for each time apartments are listed in primary column.


    Any ideas on how to just print once the MAX? Maybe a DISTINCT?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!