+ Reply to Thread
Results 1 to 12 of 12

quickly search most item that appear in an autofiltered table

  1. #1
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    quickly search most item that appear in an autofiltered table

    hi,

    how do you quickly search the popular item or the item that appear the most in an autofiltered table?

    ex:

    i have a autofiltered table from column A to D
    i have item names in column C
    i want to know which item name appear the most in column C
    how do you quickly know which item name it is?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    Add a helper column and count the entries, then check/filter that for the highest
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    Re: quickly search most item that appear in an autofiltered table

    hi,

    thanks for the reply

    edit:
    so i figured out how to get the unique item names in a helper column by DATA --> Advanced Filter --> check box unique records only -- into column F

    then I found out about the COUNTIFS function and use a formula in column G that uses the range in list of column C and the criteria in column F
    ex:
    range in C is C2:C25
    formula in G is COUNTIF($C$2:$C$25,F2) -- retain the $ sign so the range wont change when copied to other rows in G
    Last edited by k1dr0ck; 07-26-2019 at 03:43 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    Great work!!

    You could also probably have used a countif in the helper as well, to keep it dynamic

  5. #5
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    Re: quickly search most item that appear in an autofiltered table

    use a countif to retrieve the unique items names?
    how would the formula look?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    Something like this, assuming your data is in col B

    =if(countif($B$2:B2,B2)>1,0,1)
    copied down

    This will create a list of 1's (unique values) and 0's (duplicate values)

  7. #7
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    Re: quickly search most item that appear in an autofiltered table

    i see, thanks

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    Let me know how you make out if you try that approach

  9. #9
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    Re: quickly search most item that appear in an autofiltered table

    is it possible to combine below

    COUNTIF($B$2:$B$25,F2)

    with below

    =if(countif($B$2:B2,B2)>1,0,1)

    ex:
    list is in B and i would put the helper column in F

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    The 2nd formula is being used to ID unique records, while the 1st formula (if I understand you correctly) is counting those unique records, and as such, should probable be...
    COUNTIF($B$2:$B$25,1)

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: quickly search most item that appear in an autofiltered table

    Come to think of it, if all you really need to do is count unique records, maybe this...
    F2=if(countif($B$2:B2,B2)>1,0,1)+F1
    copied down

    You would then just need to either just reference the last cell in that helper, or use MAX() on the helper

  12. #12
    Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2016
    Posts
    530

    Re: quickly search most item that appear in an autofiltered table

    sorry i don't get it

    also i tried again with first solution and using the DATA--Advance Filter-- unique recordsd only
    it retrieved a record twice (column K i sample file)

    i've attached my sample file
    Attached Files Attached Files

+ 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. [SOLVED] Search for item in multipage and show the item on the specific page + Collection
    By alexgoaga in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2019, 05:37 PM
  2. Search Invoice No quickly and highlight
    By majidsiddique in forum Excel General
    Replies: 3
    Last Post: 01-16-2019, 07:04 AM
  3. VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!
    By JamesGoulding85 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2014, 06:15 AM
  4. UserForm: Search Item in List > Call Table associated with Item > Execute Command
    By TexasAggie12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 12:58 PM
  5. [SOLVED] Search number quickly
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-28-2013, 09:01 PM
  6. Replies: 2
    Last Post: 02-29-2012, 03:57 AM
  7. Replies: 2
    Last Post: 10-24-2008, 08:36 PM

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