+ Reply to Thread
Results 1 to 6 of 6

Extracted names from list if value greater than 2

  1. #1
    Registered User
    Join Date
    10-31-2012
    Location
    Hemel Hempstead
    MS-Off Ver
    Excel 2003
    Posts
    11

    Extracted names from list if value greater than 2

    Hi Guys

    I have created a spreadsheet which has a 100 names in coloumn B, then Numerical Data from D-J With the Total in Coloumn K. (In Worksheet 2)


    On Worksheet 1 I have created a summary page.

    In Cell A I have the following formula: (Counting how many cells in Coloum K are greater than 2)

    =COUNTIF(Worksheet2!K3:K100,">2")

    For example if there was 4 out of 100 greater than 2 could I list there names under cell A in worksheet 1 from the data in Coloumn B worksheet 2, like below:


    Greater than 2
    4
    Mike
    John
    Steve
    Carlos


    Any Help would be much appreciated

    Thanks in Advance
    Majort
    Last edited by MajorT; 10-31-2012 at 10:06 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Extracted names from list if value greater than 2

    Hi Majort,

    In cell A1 on Sheet1

    =COUNTIF(Sheet2!$K$2:$K$13,">2")

    Then in Cell A2 on Sheet1

    =IF(ROWS($A$2:A2)<=A$1,INDEX(Sheet2!$B$2:$B$13,MATCH(0,IF(Sheet2!$K$2:$K$13>2,COUNTIF($A$1:A1,Sheet2!$B$2:$B$13)),0)),"")

    Expand the range to fit your data and then drag down until you get blanks

    IMPORTANT
    This is an array formula
    Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
    If entered correctly, the formula will be enclosed in {brackets}
    Do not enter the {brackets} manually
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-31-2012
    Location
    Hemel Hempstead
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Extracted names from list if value greater than 2

    Fuel Check updated sample.xls

    I am trying to add the names in the cells below B9 from the summary tab.

    Thanks
    Attached Files Attached Files
    Last edited by MajorT; 10-31-2012 at 09:23 PM.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Extracted names from list if value greater than 2

    Sorry, took a few extra minutes until I figured out you had two Tinkler's so I combined the names in a helper column.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-31-2012
    Location
    Hemel Hempstead
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Extracted names from list if value greater than 2

    Sorry Fell Asleep, Its late here.

    Thanks You Very Much

    Thats Brilliant

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: Extracted names from list if value greater than 2

    You are welcome. Thanks for the feedback.

    ===========================================================================

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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