+ Reply to Thread
Results 1 to 10 of 10

Assign a number based on criteria in two columns

  1. #1
    Registered User
    Join Date
    01-15-2018
    Location
    New Zeland
    MS-Off Ver
    2016 Professional Plus
    Posts
    23

    Assign a number based on criteria in two columns

    Hello,
    Id like to automate the assigning of a number in Column C for each person added to the spreadsheet. Annually there are ~900 people added. The number generated is based on the following.
    Column D variables are: 5, 6, 7 or 8
    Column E variables are: F or M

    The first number for each combination are:
    5F=1000
    5M=2000
    6F=3000
    6M=4000
    7F=5000
    7M=6000
    8F=7000
    8M=8000

    The first Cell is C2

    On the attached txt file I went through the possible combinations for the first two rows. I can get the numbers to increase with a looping 5F=5F+1 for example but that's about as far as I get to automating the number calculation unfortunately!
    Any help on how to achieve this would be greatly appreciated.
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Assign a number based on criteria in two columns

    Hi
    Try this in C2

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-15-2018
    Location
    New Zeland
    MS-Off Ver
    2016 Professional Plus
    Posts
    23

    Re: Assign a number based on criteria in two columns

    Thank you. I appreciate you looking at this for me. It's not quite what I need unfortunately.
    I have attached a file with 2 columns of demo data, what I need in the desired outcome column and your formula in Column C.
    Hopefully it shows the required numbering scheme more clearly.
    Thank you once again.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-26-2018
    Location
    Kansas, USA
    MS-Off Ver
    Excel 2016, 365
    Posts
    28

    Re: Assign a number based on criteria in two columns

    I used VLOOKUP and CHOOSE
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    :: BatNoob
    ' BatNoob
    /* BatNoob*/

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,911

    Re: Assign a number based on criteria in two columns

    Try this ...

    =D2*2000-IF(E2="M",8000,9000)+COUNTIFS($C$1:C1,C2,$E$1:E1,E2)

  6. #6
    Registered User
    Join Date
    01-15-2018
    Location
    New Zeland
    MS-Off Ver
    2016 Professional Plus
    Posts
    23

    Re: Assign a number based on criteria in two columns

    Thank you for this.
    It appears the number remain at the thousand rather than increasing. ie 5M = 2000, next 5M = 2001 etc
    I'm sorry if I am not explaining this well.
    Thank you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-15-2018
    Location
    New Zeland
    MS-Off Ver
    2016 Professional Plus
    Posts
    23

    Re: Assign a number based on criteria in two columns

    Thanks Phuocam.
    When I paste this in C2 I get a warning about a circular reference. The result in C2 = 0
    Thank you for looking at this.

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,911

    Re: Assign a number based on criteria in two columns

    Edit:

    =D2*2000-IF(E2="M",8000,9000)+COUNTIFS($D$1:D1,D2,$E$1:E1,E2)

  9. #9
    Registered User
    Join Date
    01-15-2018
    Location
    New Zeland
    MS-Off Ver
    2016 Professional Plus
    Posts
    23

    Re: Assign a number based on criteria in two columns

    Thank you Phuocam.
    That is perfect. I'm amazed at how compact the formula is to achieve what we are after.
    If you have the time and energy I'd be very interested to try and understand it.
    why *2000? Why only include 8000 and 9000 and not the other starting numbers.
    That sort of thing.
    Perfectly understand if you are not able to.
    Thanks once again and also thanks to everyone who posted a reply.
    Cheers.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Assign a number based on criteria in two columns

    Just for fun if someone is interested in...

    via PowerQuery

+ 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. Replies: 3
    Last Post: 08-24-2017, 05:59 AM
  2. Replies: 4
    Last Post: 07-13-2017, 05:45 PM
  3. Smallest Number based on criteria from several columns
    By qwertyyy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2015, 11:32 PM
  4. Replies: 2
    Last Post: 08-12-2015, 01:01 AM
  5. [SOLVED] Count number of occurrences based on criteria in multiple columns and rows
    By gerryger in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2015, 08:13 PM
  6. sorting columns based on row criteria, with variable number of columns
    By bardobhb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-02-2014, 04:02 PM
  7. Calculate number of days between 2 dates & then assign a number based on the answer
    By MrHappyGoLucky12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-27-2014, 09:20 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