You are here:

Excel/Excel-2007 macro for conditional adding

Advertisement


Question
QUESTION: Dear Sir,
May I request your help on this macro please"
I want to auto-number col L named JB Terminal using the formula $L(N)=$L(N-1) + $I(N-1) starting from N=5 (Row number)

Name of worksheet: MasterList It already has a macro for Worksheet change event.

Conditions: Check for Col K. $K5 takes values as a result of concatenation of answers to two questions in the Msg box.
Q1: Enter Junction Box Type: JB_A1 / JB_A2 / JB_B1 / JB_B2
Q2: Enter Junction Box No: Any numerical value, like 2, 32, 741 etc (max 999) 
So, $K5 = 1JB_A1 or 32JB_A1 or 76JB_A2 or 872JB_B2 etc.

CONDITION 1:
If the value found in $K5 is new (not found earlier in the col), then $L5 = 1. for ex: If 
K5 = 1JB_A1 then L5 =1
K45 = 2JB_A1 then L45 =1
K111 = 3JB_A1 then L111=1 (Note that 3JB_A1 <> 1JB_A1)
K137 = 1JB_A2 then L137=1 (Note that 1JB_A1 <> 1JB_A2)
K147 = 3JB_A2 then L147=1 

If the value found in $K6 is not new, $K6= $I5 + $K5 for example, let us consider the above values again as below:
(Note that $I5 will be either 2 or 3 or 4 selected by DD list, In all examples below I have considered $I5 =2)

K5 = 1JB_A1 then L5 =1
K6 = 1JB_A1 then L6 =3
K7 = 1JB_A1 then L7 =5
K8 = 1JB_A1 then L8 =7
K9 = 1JB_A1 then L9 =9
K10 = 1JB_A1 then L10 =11
K11 = 1JB_A1 then L11 =13 etc

similarly, 
K45 = 2JB_A1 then L45 =1
K46= 2JB_A1 then L46 =3
k47 = 2JB_A1 then L47 =5
k48= 2JB_A1 then L48 =7

and so forth.

CONDITION 2: for maximum value in $L6
The idea is to restrict the added value in L in accordance to which type of Junction Box is used 
For type JB_A1, maximum value of addition is 18
For type JB_A2, maximum value of addition is 30
For type JB_B1, maximum value of addition is 24
For type JB_B2, maximum value of addition is 40

Procedure: If the count value exceed 18, and if the type of Junction is 'xJB_A1' (regardless of any value of x (x=enter junction box number) then
SUB1: MSG Box Max Terminals for the selected type of Junction Box is exceeding it's Limits. Please select a different type of Junction Box or Please select a new number of junction box. On clicking OK, the active cellwill be the cell where the new type / number is selected. ie, cell K.
If the count value exceed 18, and if the type of Junction is NOT 'xJB_A1' then continue the addition till the count exceeds 30. Again, 
If the count value exceed 30, and if the type of Junction is 'xJB_A2' (regardless of any value of x (x=enter junction box number) then Go to SUB1.
continue this till addition exceeds 40. 

Please advise me if you have any questions.
Best regards
Vilas Desai

ANSWER: To create a MACRO that does all this would probably require some compensation of some sort.  That's a tall order.

You can accomplish most of what you've depicted using FORMULAS.

To get your first condition, try this in L5, then copied down...we're not done.

     =MAX(COUNTIF($K$1:$K5,$K5)*2-1, 0)

Now, to limit that to 18, we add this:

     =MIN(MAX(COUNTIF($K$1:$K5,$K5)*2-1, 0),18)

Now, to make the 18 variable, we check the last two letters of K5.  This formula does that:

     =MATCH(RIGHT(K5,2), {"A1","A2","B1","B2"}, 0)

To convert that into the limits, we add this to that formula:

     =CHOOSE(MATCH(RIGHT(K5,2), {"A1","A2","B1","B2"}, 0), 18, 30, 24, 40)

Now we merge that back into original formula in place of the 18:

    =MIN(MAX(COUNTIF($K$1:$K5,$K5)*2-1, 0), CHOOSE(MATCH(RIGHT(K5,2), {"A1","A2","B1","B2"}, 0), 18, 30, 24, 40))


Lastly, I would recommend some Conditional Formatting in column L to simply detect these even numbers.  Since the formula only creates ODD numbers and the MAX values are even, a simple =ISEVEN() test to color any cell with an even number to draw attention to it.

---------- FOLLOW-UP ----------

QUESTION: Dear Jerry
Than you so much for your efforts. As you mentioned, the formula does almost everything that was need. I tested your formula, and works perfect but with these points missing.
1. It does not check Value of $I5. I inserted $I5 in place of '2' in the formula and observed it works good only if $I5=2 not if $I5= 3 or 4
2. On reaching the max  value, it does not prompt the user that the count (in Col L) exceeds max value and to  either enter a new  Junction Box type (Q1 as above) or enter a new junction box no.(Q2 as above)When the user chooses a new Junction box type or number, the col L value should start from 1 and increase withe formula $L(n) = $I(n-1) + $L(n-1)

I greatly appreciate your help in coming so far, but he above corrections will be extremely useful.

Thanks and best regards
Vilas Desai

ANSWER: 1) The meaning and impact of I5 is not clear and was thus not included in the formula suggestion.   The meaning an impact of I5 is still not clear.

2) I addressed the "alert" issue in my original formula.  I suggested applying an ISEVEN Conditional Formatting on column L to highlight cells that have reached their maximum, and any further duplicates would also be highlighted, easy enough to spot when it occurs.

Picture:  http://screencast.com/t/kwOebhRt

---------- FOLLOW-UP ----------

QUESTION: Dear Sir,
Thanks again. $I5 decides the number that is added to $L5 to give $L6. The possible options of $I5 are 2/3/4 selectable by drop down list. Initially, the Value of L5 must be 1 because the value in K5 is not found any where else in col K.
In your formula, I tried replacing '*2' with '*$I5" but the problem is if I5= 3 or 4  then  L5 does not start with 1 It will start with 2 or 3 . In your formula, you have considered $L5 as a fixed value of 2 but it is any value between 2, 3 or 4.
Therefore, with I as 2/3/4, L can take even values also.
I understand your idea of finding duplicates which I think I can do.
Please advise
Best regards
Vilas Desai

Answer
So if I5 is 4, L5 is still 1 due to the start of the list.
If the next instance of K5 doesn't occur until K10, then the value of L10 is I5+L5?
Then if it doesn't occur again until K50, the value in L50 would be I10+L10?

I'll see what I can do.   If the values going down column A can be even numbers before we get to the MAX, the CF formula trick I suggested won't work that simply.  I was suggesting looking for any EVEN number and coloring it.  I'll think about it.  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.