Forum Discussion

Svetik's avatar
Svetik
Copper Contributor
Sep 15, 2023

How to find values that repeat x times

Hello to everybody!

 

Help me figure out if it is possible to find cells in Excel using built-in tools or functions that are repeated x times. I have a large book of clients and am currently working on a loyalty program. I need to know which of them made 5 purchases, which made 7, and which made 10. I know that it is possible to set the value of a cell and see how many times it appears in the document. But I have more than 3,000 clients, and searching for information on each one is a huge job. So I would like to enter a query like "Find cells whose value is repeated 10 times" - and see the results.

 

Thank you a lot!

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 15, 2023

    Svetik 

    =TEXTJOIN(", ",,DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(A1:A15))),

    LAMBDA(x,y,VSTACK(x,IF(COUNTIF(A1:A15,INDEX(UNIQUE(A1:A15),y))=C1,

    INDEX(UNIQUE(A1:A15),y),"")))),1))

     

    With Office 365 or Excel for the web you can apply this formula.

     

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Sep 15, 2023

    Hi Svetik,

    You can try to use COUNTIF function to achieve your goal:

    COUNTIF function - Microsoft Support

    1. Organize Your Data

    Organize all your client data for example in a single column. Let's say your list starts from cell A1.

    2: Count the Occurrences

    In any empty cell, use the `COUNTIF` function to count how many times a specific value repeats in your data. For instance, if you want to find clients who made 10 purchases:

     

    =COUNTIF(A:A, "10")

    * you can replace "10" with the value you want and then press Enter to get the result (count).

    This formula tells Excel to count how many times "10" appears in column A.

     

    3: Filter the Results

    To filter clients who made (for example) exactly 10 purchases, follow these steps:

    1. Click on the header of the column containing your client data (e.g., column A).

    2. Go to the "Data" tab in Excel.

    3. Click on the Filter icon on the column header you selected.

    4. A dropdown menu will appear in the column header. 

    5. From the submenu, select "Equals" or "Custom Filter," depending on your preference.

    6. In the dialog box that pops up, enter the count you obtained from the `COUNTIF` function (e.g., 10).

    7. Click "OK" or "Filter."


    Excel will now display only the rows where clients made exactly 10 purchases. You can repeat these steps for different purchase counts, such as 5 and 7, by adjusting the value in the `COUNTIF` formula and applying the filter accordingly.

     

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

Resources