+ Reply to Thread
Results 1 to 6 of 6

Assign certain nr. to a combo of def. variables until a new combo appears then change nr

  1. #1
    Registered User
    Join Date
    07-28-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    Assign certain nr. to a combo of def. variables until a new combo appears then change nr

    Dear all,

    I am not at all an excel expert and hope that someone will be able to help me on this. Thanks in advance for that. I am sure it aint a great deal for you guys.
    So that is the deal. I have a two column excel sheet with, each raw containing a Combo of a true and false argument. The possible combinations in my case are "only" true&false, false&false and false&true.F
    For example:

    Colum1 Colum2 Colum3
    True False
    False False
    False False
    False False
    False False
    False True
    False False
    False False
    False False
    False False
    False False
    True False
    False False
    False False

    Now I would like to have a number assigned to each combination in the third column. Lets say nr. 1 to the True&False Combo. The crutial Point is now that I want nr. 1 to be assigned as well to the raws to come after a True&False Combo is detected, until there is a False&True Combo given. The later situation would leads to the nr. 2 being assigned next to this Combo (in the third column). So again Nr. 2 would be assigned to all the following raws, as Long as there is a True&False Combo identified again, what leads to Nr. 1 being assigned to column 3 again and so on.
    So for the example above this would mean the following outcome:

    Colum1 Colum2 Colum3
    True False 1
    False False 1
    False False 1
    False False 1
    False False 1
    False True 2
    False False 2
    False False 2
    False False 2
    False False 2
    False False 2
    True False 1
    False False 1
    False False 1


    I hope I was clear enough and this can be constructed somehow. Otherwise I have a huge Problem

    Greetings

    Alf

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,891

    Re: Assign certain nr. to a combo of def. variables until a new combo appears then change

    Problem here is that there is no direction of what should happen if the first combo is false/false? If that is not a issue, that is if the first combo will always be false/true or true/false, then the following formulas will do what you want:
    Please Login or Register  to view this content.
    for the first pair and
    Please Login or Register  to view this content.
    for the second and down.
    If you would put a blank row at the top of your combo columns then you would only need the second formula as shown in the attached file.

    Combo counts.xlsx

    Hope this helps.

  3. #3
    Registered User
    Join Date
    07-28-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    Re: Assign certain nr. to a combo of def. variables until a new combo appears then change

    First of all great work! Your provided excel sheet works perfectly. Many thanks for that! Also good starting remark. Is it possible that column 3 starts to assign either nr. 1 or nr. 2 not before there is any true in one of the two first columns. So as an example:

    Column1 Column2 Column 3
    false false 0
    false false 0
    false false 0
    true false 1
    false false 1
    false true 2
    false false 2
    false false 2
    true false 1
    false false 1
    etc.

    Greetings,

    Alf

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,891

    Re: Assign certain nr. to a combo of def. variables until a new combo appears then change

    You could change the formula to:
    Please Login or Register  to view this content.
    The OR(C1="",C1=0) part takes care of the case where A2 and B2 are FALSE and C1 is in the empty row, or the case where the cell above already has a value of zero.

    If this answers is satisfactory then please mark the thread 'Solved'. If anyone provided useful information, please consider adding to their reputation. Hope that you have a good day.

  5. #5
    Registered User
    Join Date
    07-28-2015
    Location
    Amsterdam
    MS-Off Ver
    2013
    Posts
    3

    Re: Assign certain nr. to a combo of def. variables until a new combo appears then change

    Dear JeteMc

    First of all thanks for replying again. Could you again please send me short excel sheet with the formula in it? I can not fully follow the abovementioned add-on.
    Thanks upfront for that. I will change threads ticker to solved afterwards.

    Greetings

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,891

    Re: Assign certain nr. to a combo of def. variables until a new combo appears then change

    Here is the file using the formula from post #4:

    Combo counts.xlsx

    Hope this helps.

+ 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] Excel 2010 VBA – Combo Box Options– Clear cell/box in 2nd Combo when 1st Combo selected
    By theshybutterfly in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2013, 07:25 PM
  2. HELP: How to find which combo appears most often ?
    By Mr.Nob0OOdy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2013, 04:04 AM
  3. [SOLVED] assign macro to activeX combo box
    By Blake 7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2012, 11:19 AM
  4. Assign named range in worksheet to combo box
    By AppSupportKarl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2011, 06:38 AM
  5. Assign macro to combo box
    By Mike Stephens in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-09-2011, 02:18 PM
  6. Combo box: message appears with click
    By help000 in forum Excel General
    Replies: 1
    Last Post: 07-31-2008, 06:31 PM
  7. Problems with cascading combo box, appears to only be 1 column's numeric values (zipcodes)
    By tHeRoBeRtMiTcHeLL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2005, 01:10 PM
  8. Assign macro to each item of a drop-down list or combo box - how?
    By jbp20717 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2005, 07:06 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