+ Reply to Thread
Results 1 to 10 of 10

Display Most Appearing Text and the number of times that text appears

  1. #1
    Registered User
    Join Date
    10-15-2015
    Location
    Milwaukee
    MS-Off Ver
    2013
    Posts
    5

    Display Most Appearing Text and the number of times that text appears

    excel help.JPG

    The final result should be
    Bringing the Most: Bob
    Number of Items: 6

    Thanks for the help!

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Display Most Appearing Text and the number of times that text appears

    If you post a workbook with some sample data and the results you expect it will be much easier to help you.

  3. #3
    Registered User
    Join Date
    10-15-2015
    Location
    Milwaukee
    MS-Off Ver
    2013
    Posts
    5

    Re: Display Most Appearing Text and the number of times that text appears

    Example File.xlsx

    Here is the example file that I posted. Thanks!

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Display Most Appearing Text and the number of times that text appears

    Select and copy all the names and paste into a column and use Data Tab, Remove Duplicates to form a list of names without duplicates. I used H3:H7 for the final list of names. I I3 enter this formula and fill down to give the count of times each name appears in the data.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In D13 where you want the answer to Bringing the most enter this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the number of items enter this formula in D14
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    B
    C
    D
    E
    F
    G
    H
    I
    3
    BBQ
    Birthday Party
    Joe
    2
    4
    Items Name Items Name Bob
    6
    5
    Ketchup Joe Cake Dave Sue
    2
    6
    Hot Dogs Bob Hats Don Dave
    2
    7
    Hot Dog Buns Bob Glitter Dave Don
    1
    8
    Soda Sue Candles Bob
    9
    Forks Bob Soda Bob
    10
    Salad Bob Ketchup Joe
    11
    Cake Sue
    12
    13
    Bringing the Most Bob
    14
    Number of Items
    6
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Display Most Appearing Text and the number of times that text appears

    Two options here. One of them looks like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-15-2015
    Location
    Milwaukee
    MS-Off Ver
    2013
    Posts
    5

    Re: Display Most Appearing Text and the number of times that text appears

    I like your solution doverman, but the data set that I'm dealing with changes quite frequently and is also quite large. For example, Bob calls me up and says he can no longer come to the either event. Other people take over for him, and a new person (Anne) is added. Also now both Sue and Joe are both bringing 4 items. Ideally, I'd like Bob to automatically be removed and Anne to automatically be added. Thus the copy and paste method would no longer work.

    excel help2.JPG

    Example File2.xlsx

    Is there a way to always copy data from one column and paste it into another column? My number of Items will never change.

  7. #7
    Registered User
    Join Date
    10-15-2015
    Location
    Milwaukee
    MS-Off Ver
    2013
    Posts
    5

    Re: Display Most Appearing Text and the number of times that text appears

    I think you're on to something Jacc. However, I need know who is bringing the most items to both events. In this file it is clearly Sue and Joe. That's kind of why I asked doverman about copy columns into one big column so that you could perform the formulas in C14/C15 and E12/E13 just for both events.

    excel help3.JPG

    Example File, Jacc_changed.xlsx
    Last edited by bkpaguy; 10-16-2015 at 11:04 AM.

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Display Most Appearing Text and the number of times that text appears

    I made a massive change to the way that your data is entered. I made a table out of both ranges and then created a Pivot table based on that table. Now when you add names to the list the Pivot table can be updated by right clicking in the pivot table and clicking on refresh. This will give you a listing of the names in alpha order and the number of items for each name. Beside the pivot table, I have extracted the max number of items brought by an individual and beside that the list of names who may have brought that same amount.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-15-2015
    Location
    Milwaukee
    MS-Off Ver
    2013
    Posts
    5

    Re: Display Most Appearing Text and the number of times that text appears

    Awesome doverman! I've never worked with a pivot table before so that is new to me, but I believe this will work for my application. How could I forget that Alex was bringing the beer! Thanks a million!

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Display Most Appearing Text and the number of times that text appears

    Thank you for the feedback.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with counting the number of times a certain text appears
    By buckles23 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-04-2014, 11:53 AM
  2. Add the number of times text appears in column
    By paladin7 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-09-2014, 06:11 AM
  3. Count the number of times a particular text appears in group of cells
    By L.LEE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2013, 04:16 AM
  4. Count the number of times a specific text appears in column D
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2011, 02:02 PM
  5. Replies: 6
    Last Post: 01-30-2008, 01:30 PM
  6. display and count the number of times a value appears
    By dbath in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-23-2006, 01:00 PM
  7. [SOLVED] Search text & record number of times it appears
    By bizju@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1