+ Reply to Thread
Results 1 to 5 of 5

Number items in a list that are NOT chosen

  1. #1
    Registered User
    Join Date
    09-01-2019
    Location
    Singapore
    MS-Off Ver
    Office 365 ProPlus
    Posts
    12

    Number items in a list that are NOT chosen

    I have a list of names. For each name in the list, the cell to the left of the name will either be blank, or marked with an "X". In the column to the right of the list, I need a formula that will number the names in order starting with "1" at the top, but will EXCLUDE all of the names with "X" in the cell to the left. The result is that when I "choose" a name from the list by marking it with an "X", the name will be removed from the rankings.

    For example, my top 5 names in the list will have numbers 1-5 marked in the column to the right, but when I put an "X" beside the 3rd name in the list, the cell to the right of the 3rd name will become blank, and the 4th name will then take the number 3, and all names below that will have their numbers change in order from 4 to the end of the list.

    I'm attaching a file that shows what I want the result to look like, but I need a formula to make it work.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,689

    Re: Number items in a list that are NOT chosen

    In D2 copied down:

    =IF(B2="",COUNTIF(B$2:B2,"<>X"),"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,689

    Re: Number items in a list that are NOT chosen

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

  4. #4
    Registered User
    Join Date
    09-01-2019
    Location
    Singapore
    MS-Off Ver
    Office 365 ProPlus
    Posts
    12

    Re: Number items in a list that are NOT chosen

    This worked! Thank you!

    I decided to add some complexity to this, which lead me to modify your formula to :

    =IF(B2="",COUNTBLANK($B$2:B2),"")

    This is because I changed the criteria from "X" to any number, so I needed the formula to simply count the blank cells.

    Now have another question that I'm hoping can be solved with a simple formula rather than a bunch of nested vlookups:

    I've created a sheet called "Names" where I have created 5 separate lists of names. Each name is unique (no duplicate names). On my sheet named "Development," I want column B to search the entire "Names" sheet for the name in Column C and return the data in the "Order #" column (to the right of the name) from the "Names" tab (or an blank cell if Order # is blank).

    Thanks.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,689

    Re: Number items in a list that are NOT chosen

    You will need to start a new thread for this new query with an appropriate title, please. This thread will be marked as solved.

    No help to be given for this query in this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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