+ Reply to Thread
Results 1 to 2 of 2

Return defined number of matches in table

  1. #1
    Forum Contributor
    Join Date
    08-10-2009
    Location
    nottingham
    MS-Off Ver
    Excel 2010
    Posts
    110

    Return defined number of matches in table

    Hello,

    Can someone point me in the right direction please; I'm sure i'm not phrasing my question correctly so struggling to find any help...

    I'm trying to get a formula to return a defined number of matches in a table. For example; if you have ten people listed in column A from 1 to 10 and next to them in column B is their preferred drink (lets say wine or beer). This just happens to be split into 4 wines and 6 beers - i want to be able to use a formula to return the names of the top 3 people who want beer, if I define in another cell that only 3 beers are available.

    My intention is to another formula to "mark" these chosen people and put them to the bottom of the list so next time, the people who didn't get a beer this time will get priority next time over those who did.

    i keep getting results in my searches to do with index/match formulas but not sure this is exactly what i need.

    any suggestions on where i should be looking?

    Thanks

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Return defined number of matches in table

    Suppose you have Beer or Wine in column B, starting with cell B2. Then you can have this formula in C2:

    =IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

    and copy this down. It will repeat the drink from column B and tag on to the end of it a running count of the number, so Beer_1, Beer_2 and Beer_3 can easily be identified as the top-3 beer drinkers.

    Does that help you? (I'm not really sure what you want after that - maybe posting a sample workbook might help).

    Pete

+ 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. Search a table with multiple search inputs and return all matches
    By JDI in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-16-2014, 12:23 PM
  2. [SOLVED] Return a value if a number is in a range, even, and then matches the street
    By pixelninja in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-16-2014, 05:45 PM
  3. Combine VLOOKUP and SUMIF to return sum of user-defined ranges in a table
    By sciondraconis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2014, 03:27 AM
  4. Find all matches for one cell and when a match found return a ref number VBA
    By ch_abs in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2014, 06:15 AM
  5. find and return multiple matches in a table, then sum and report total
    By cyclops755 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-08-2013, 06:57 PM
  6. Replies: 5
    Last Post: 01-05-2013, 01:28 AM
  7. [SOLVED] user defined function to return a string value based on two column matches
    By Ed C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-14-2012, 11:02 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