how to populate a column with a string taking in consideration 5 different criteria

  • I need help to populate the Column C on sheet named Interdiction Review with a string "Consider" (need to run the code for sheet interunion review be created)

    the code is placed on Thisworkbook


    on Column C need to be populate with a string "Consider" is:

    - consumer has only 1 input (this is done)

    - consumer has 2 to 4 inputs and total volume is <10K

    - consumer Level (based on the rules below) is Level 2 or Level 3 (columns with levels values are CV and CW from Start sheet)

    - If Review Period is = 60 days and the maximum input date is older than 30 days -- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)

    -If Review Period is = 1year and the maximum input date is older than 90 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)

    - f Review Period is = 5year and the maximum input date is older than 180 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)


    The problem is how to populate the column C taking in consideration so many criteria in a way that the code will not transposes each other .

    I tried to use excel formula on VBA, however I cold not get the result I wished.

    what would be the best way to do it? how should I start?


    7exemplo. xlsm (1).xlsm



    for - consumer has only 1 input (this is done) criteria the code used was:



    not sure if I could use the code above for all the criteria at the same time.



  • Hello all,


    I managed to continue the code and to add more criteria to it.


    What I am missing is to link the consumer Level with the last date of his/she input.
    60 days and the maximum input date is older than 30 days and the consumer Level is 2 or 3

    1 year and the maximum input date is older than 90 days and the consumer Level is 2 or 3

    5 year and the maximum input date is older than 180 days and the consumer Level is 2 or 3


    any advises how to do it?


    thank you

  • Would testing the Start tab columns CV and CW give you the results you want?

    I Tried to and I created a new part for the code, however it is taking the information from the wrong column and I dont know why. 13.trytryagain.xlsm



    The criteria I have for this code to run and add "Consider" in the column C are:


    If consumer meets one of these rules, value should be set to Consider:

    • Consumer has only 1 Transaction -- (is done)

    • Consumer has 2 - 4 Transactions but total volume < 10,000 USD --- (is done)

    • Consumer Level (based on rule below) is Level 2 or Level 3 --- ( this information are on column CV and CW)

    • If dropdown is 60 Days and max transaction date is older than 30 days

    • if dropdown is 1 year and max transaction date is older than 90 days

    • If dropdown is 5 years and max transaction date is older than 180 days


    It seems that my code is looking in the wrong column. ex:
    client number 3 are on columns H and AJ (so for this client need to check the column CV when checking the lines where he is on column H; and CW when the client number are on column AJ (if client number 3 will be Level 2 or Level 3 on column CV or CW then it need to have a consider). However, if you filter the column H and later the column AJ the client number 3 level will always be Level 1.



    The level for column CV is when the client number is on column H

    The level for column CW is when client is on Column AJ

  • Does this work for you?

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Glad to hear it!

    Sr. Dangelor,


    Sorry to disturb you, but maybe could you help me to understand and to correct code with a dictionary for populating the column I on interdiction Review Tab?


    If so, in this case, should I create a new Threare?


    Thank you

  • SR. Dangelor


    Thank you so much for your time and help.

    I was able to finish the column I.

    Thank you

    What should populate column I?