Need VLOOKUP for multiple criteria

Dear experts,
In the following work sheet (this has VB also running behind), I need the following;

#01
The cell U6 returns the value (start up days) as per the the table in "categories" sheet(S2 to P9).

However, I need to combine another criteria to this using the table in categories sheet (R12 to S13). Accordingly, the resulted value in U6 should be changed. Selection criteria is in column I6 (RPT or NEW).

#02

The cell V6 should calculates the no of days the production run (example: in row 6>>> 7 days(days run) - from May 17 to May 24). Need a formula to handle this condition.


Thanks in advance
Anuruddha

Sorry, I can't see what the second condition adds to the first. Do you want to subtract a day if it is RPT?

V6 can just be =y6-x6?

Originally Posted by DannyJ

Sorry, I can't see what the second condition adds to the first. Do you want to subtract a day if it is RPT?

V6 can just be =y6-x6?

Thanks, yes. I want to subtract "a day" if it is RPT.

=IF(I6="RPT",VLOOKUP(T6,Categories!$O$2:$P$9,2,1)-1,VLOOKUP(T6,Categories!$O$2:$P$9,2,1))

Originally Posted by DannyJ

=IF(I6="RPT",VLOOKUP(T6,Categories!$O$2:$P$9,2,1)-1,VLOOKUP(T6,Categories!$O$2:$P$9,2,1))

Hi Danny,
This is just simply brilliant. It works. Thanks a lot.

However, is there any way to make the value in the U6 "zero"? I mean, by adding any criteria such as "NO" along with "RPT"?
For example, when "NO" is selected instead of RPT, the U6 value should become zero.!

Problem solved!!

=IF(I6="No",0,IF(I6="RPT",VLOOKUP(T6,Categories!$O$2:$P$9,2,1)-1,VLOOKUP(T6,Categories!$O$2:$P$9,2,1)))

Hiiii John,
Thanks for your reply.

Danny's VLOOKUP solution is working to subtract 1 day from "start up days".

As it appears, there is no effect on VBA, macro is running as it is..

Danny's reply: V6=Y6-X6 is okay. However, there is little concern when the "last day production is half, it still shows as a full day". May be there could be a complicated formula to handle this situation to give decimals, ex: 7.5 days, 7.2 days etc..

Originally Posted by Anuru

Hiiii John,
Thanks for your reply.

Danny's VLOOKUP solution is working to subtract 1 day from "start up days".

As it appears, there is no effect on VBA, macro is running as it is..

Danny's reply: V6=Y6-X6 is okay. However, there is little concern when the "last day production is half, it still shows as a full day". May be there could be a complicated formula to handle this situation to give decimals, ex: 7.5 days, 7.2 days etc..

Hi John,
Now, I noticed when "start up days" become zero, the VBA macro takes so much time to process and returns "run time error 6 and something "overflow" etc. However, this "No" option is not the right solution.

Actually, I want the first day of every line (for example: row 6, row 17 and row 24 etc) to have "zero" start up days. Because, the start of a line doesn't need "start up days" concept. It is needed when the next style is fed in the production line.

Formatting will not solve problem as the "production Days" is simply subtraction of 2 dates:

To get an "accurate" figure, the last production day is the "Qty of product produced"/Daily Production Qty"

so for row 6: the last day is 260/540 (0.48) assuming a day is 24 hours

so V6=(Y6-X6-1) + (260/540) as an example

or ...Production days

=N6/Z6 (???)

Originally Posted by JohnTopley

or ...Production days

=N6/Z6 (???)

Yes, this is the way to do it.

=N6/Z6

Thanks

Originally Posted by Anuru

Hi John,
Now, I noticed when "start up days" become zero, the VBA macro takes so much time to process and returns "run time error 6 and something "overflow" etc. However, this "No" option is not the right solution.

Actually, I want the first day of every line (for example: row 6, row 17 and row 24 etc) to have "zero" start up days. Because, the start of a line doesn't need "start up days" concept. It is needed when the next style is fed in the production line.

Hi John,
When the start up days become "zero", the VBA macro gives an error. The error is as follows;

run time error 6
overflow

kindly have a look at this please if you have time...

Originally Posted by Anuru

Hi John,
When the start up days become "zero", the VBA macro gives an error. The error is as follows;

run time error 6
overflow

kindly have a look at this please if you have time...

Hi John,
Now I am very much in need of using this work sheet for my daily work. It would be great and appreciated if you could provide a solution to the "zero" issue I have mentioned in the VBA macro.

Thanks in advance.

If SMV (T6) is set to 0 and therefore U6=0 (VLOOKUP) then M6 has DIV#0 errors as it it uses the T6 value.

So this needs addressing.

And will it only be T6/U6 set to 0?

Hi John,
Gentle reminder about the Zero issue in VBA macro..

I have attached here the most updated work sheet here.

Originally Posted by JohnTopley

If SMV (T6) is set to 0 and therefore U6=0 (VLOOKUP) then M6 has DIV#0 errors as it it uses the T6 value.

So this needs addressing.

And will it only be T6/U6 set to 0?

Sorry I didn't see the above reply...

Originally Posted by JohnTopley

If SMV (T6) is set to 0 and therefore U6=0 (VLOOKUP) then M6 has DIV#0 errors as it it uses the T6 value.

So this needs addressing.

And will it only be T6/U6 set to 0?

Hi John,
there is no "division", between T6 (SMV) and U6 (Start up). Start up days depend on the table given at P and Q columns(Categories tab). However, SMV is the criteria which determines the "number of start up days".

Read my reply: look at N6 in your latest file:

=60/U6*K6*L6*M6

U6 is SMV and SMV is 0 for 0 start up day of 0.

Set SMV (U6) to 0 .....

Originally Posted by JohnTopley

Read my reply: look at N6 in your latest file:

=60/U6*K6*L6*M6

U6 is SMV and SMV is 0 for 0 start up day of 0.

Set SMV (U6) to 0 .....

SMV is used to calculate the "target". If SMV becomes "zero", target cannot be calculated.
Only if you can change VBA macro to "accept" zero value in "start up days" column, and remove start up days (shown in red), it will be workable.

Start Up days is determined by the VLOOKUP so you cannot override it with a value: that is (part of) the problem.

Originally Posted by JohnTopley

Start Up days is determined by the VLOOKUP so you cannot override it with a value: that is (part of) the problem.

Hi John,
I manually put "0" in the start up days value(deleted the formula), then also VBA macro is not running. could there be other reason?

The macro needs changing BUT you it is not advised to override the formula: you will have to re-instate for cases where start up is not 0.

I have tried changing to decimal values such as 0.2, 0.5 etc, but the same error is coming..

Hi John,
did you have time to work out VBA coding?
I have herewith attached the most updated sheet. Still I am not able to implement this work sheet for my planning work because of the above issue in VBA.

Code was changed in my last post BUt I know start up days in column W.

I'll leave changes to you based on my last post.

Originally Posted by JohnTopley

Code was changed in my last post BUt I know start up days in column W.

I'll leave changes to you based on my last post.

Hi John,
I downloaded mod 3, but when I changed Prod start date, formula doesn't start as per that date. For example, when I changed Prd start date to May 15, the production values start from June 10...

Originally Posted by Anuru

Hi John,
I downloaded mod 3, but when I changed Prod start date, formula doesn't start as per that date. For example, when I changed Prd start date to May 15, the production values start from June 10...

Hi John,
Now, I checked again. I found these problems; the production date start issue ok.

01. The code works only in row 6 ( I changed start up days to 0 in row 8, but it doesn't change anything)
02. Although code works in row 6, after removing the start up date, production start date stays in the same position(date), it doesn't come forward. For example, if the prod start date is May 15 and has 1 start up day, after removing the start up day, it doesn't start from May 14.


02.

Originally Posted by JohnTopley

If you require more work then I suggest you contact "Commercial Services" to do the VBA.

The logic for multiple zero Start Up days needs defining and the code re-designed.

Hi John,
I checked the commercial services page. However, I don't understand the the "Points" concept. If I pay 20 $, will be able to post any number of posts/requirements within this "one month" period? Or is there any limitation?

Moreover, how is the speed of the attention on the questions and solutions? Kindly give me some idea.

Regards
Anuruddha

I have never used Commercial Services so I don't know how exactly their services are costed. However, I am sure they will give a good response providing any requirement is clearly specified.

You need to give a good sample of the multiple "Start Up Days", including for example production finishing on a Thursday, next production having 0 Start Up, but with Friday (non-work), Sat/Sun holidays so (presumably) next production start is Monday : if no holidays, then Saturday (?).

Noted and will try.

I am I correct in the logic for o Start-Up days?

For example production finishing on a Thursday, next production having 0 Start Up, but with Friday (non-work), Sat/Sun holidays so (presumably) next production start is Monday : if no holidays, then Saturday (?).

Originally Posted by JohnTopley

I am I correct in the logic for o Start-Up days?

Yes, that is exactly correct.

However, when there is a "zero" start up day for first production start date (for example row 6, row 17, row 24 etc, the Production start date should "advance" by one day(for example, when the production start date is 15 May, daily production figures should start from 15 May)

If you would have time to try again, please find the most updated work sheet below.

However, when there is a "zero" start up day for first production start date (for example row 6, row 17, row 24 etc, the Production start date should "advance" by one day(for example, when the production start date is 15 May, daily production figures should start from 15 May)

How is 15th May "advanced" when ...

production start date is 15 May, daily production figures should start from 15 May

?

I find this very confusing!

Originally Posted by JohnTopley

How is 15th May "advanced" when ...



?

I find this very confusing!

For example, in a style where 3 start up days are shown(row 6 > May 14 to May 16), and production starts on 17th. In this case, when start up days become zero, the production start date should advance/jump forward to 14th.

This scenario should happen in all the rows(row 6, 8, 10, 12, 14 etc) whenever, the start up days become zero.

Originally Posted by JohnTopley

I think your logic is wrong: we start by taking the Production Start (17th May), deducting the Start Up Days (3) to arrive at a Start Up of 14th May (assuming there are no Friday/Holidays).

If Start Up Days is 0, then Start up=Production Start (17th May) not 14th May (maybe this was a typo!?).

Let me explain the real scenario;

In practical situation, the start up days fall within the production line plan. For example, in a case where there are 3 start up days, and if the Prd start date is 15 May, the actual production comes out from the production line 18th May. Which means, 15th, 16th and 17 May are considered as stat up days.

In other words, "the planned prd start day" of the production line is 15th May. However, due to feeding time physical out put comes out on 18th May.(in this work sheet, based on the SMV, I have pre-defined how many "start up days" are required(in categories)

we start by taking the Production Start (17th May), deducting the Start Up Days (3) to arrive at a Start Up of 14th May (assuming there are no Friday/Howrong:lidays).

that is how the calculation is done, working back from the "Production Start" (column Z). We do not start from "Start Up" (column Y) and add the "Start Up Days" in column Y.

As you have not answered my question i.e how can Start Up" be 14th May?, it will be better that you hand this over to "Commercial Services" as I have spent a great deal of time of a constantly changing scenario (all for free!).

Originally Posted by JohnTopley

that is how the calculation is done, working back from the "Production Start" (column Z). We do not start from "Start Up" (column Y) and add the "Start Up Days" in column Y.

As you have not answered my question i.e how can Start Up" be 14th May?, it will be better that you hand this over to "Commercial Services" as I have spent a great deal of time of a constantly changing scenario (all for free!).

If Start Up Days is 0, then Start up=Production Start (17th May) not 14th May (maybe this was a typo!?).

The above logic is correct. If u can work accordingly, pls try.

See attached .....

Originally Posted by Anuru

If Start Up Days is 0, then Start up=Production Start (17th May) not 14th May (maybe this was a typo!?).

The above logic is correct. If u can work accordingly, pls try.

If the VBA works for "ZERO" start up days, the problem is solved.

Originally Posted by JohnTopley

See attached .....

Brilliant John. It is working now.
Thanks a lot for your kindness and hard work in working out this project.
May good health, happiness and prosperity be with you all the time.

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