VBA Code for Largest Pull

[spoonedmango]

I'm a self taught VBA person, so please be gentle with me!

I've been lurking the boards for a while, this is my first post.

I need to find the largest pull. I'm not sure if this is a common term, so I'll explain it the best I can.

For each part number there is a number representing the # of days. I think this is a lead time, but I'm not positive. We run data from the system telling us how many parts we've shipped per day for what ever time frame the manager determines. That means there could be 30, 60, 90, or any number of days data.

I have to take the number of days for each part, go to the first day that has shipped data, count from that day out however many the # of days is and sum the parts shipped for that range. Starting at the end of the first range, go to the next day that has shipping data, and do the same thing; until you reach the end of the data. If you only have 10 days left, but your # of days is 30, then you just use the 10 days.

The largest pull is the range with the largest sum.

We have been doing this manually for thousands of part numbers over many locations. I know there's a way to use VBA to help with this, I just can't seem to put the pieces together to do it.

I've atttached a sample file that hopefully make my explanation make sense. The largest pull is already in, it was done manually.

Thank you in advance for any assistance!LP Sample for Online.xlsx

[TMS]

Try, in cell C2:

=SUM($D2:INDEX($D2:$IV2,$B2))

and copy down.


Regards, TMS

[spoonedmango]

TMS,
Thank you!

It mostly works! Yeah! That means I'm a step closer, thank you!!!

Some of the manual largest pull numbers on the spreadsheet were incorrect. After making corrections and adding the formula you gave me, I have some differences. I can account for some of them. The formula will put 0 if there is only one value. The rest I have no idea why they would not be the same.

I attached the updated file.

Again,
Thank you in advance for any assistance.

[TMS]

Thanks for the rep.

Take a close look at the number of days in column B. See the problem? Try increasing the number of decimal places. Now can you see the problem?

This will address the problem: =SUM($E3:INDEX($E3:$IV3,ROUNDUP($B3,0))) ... where it's one value out.

However, it doesn't account for all the anomalies ... I just don't get the manual figures

Regards, TMS

[spoonedmango]

Thank you TMS!!!! I never even thought to look for partial numbers.

I solved the 0 values with an IF/THEN.

I think I understand the discrepencies. Two things, 1) The formula is starting in E, regardless if the cell has a value or not. I need to be able to start in the first cell of that row that has a value, then begin counting the number of days. (An example of this would be Row 16.)

2) It looks like the formula is taking only the first set of days. (An example of this would be Row 4.)

I have no idea how to make it start on the first cell with a value, but can I use REPT to make the formula go to the end of the row?

My appologies if I am asking basic questions, your patience is greatly appreciated!

I've uploaded an updated file.

Thank you!

Update B LP Sample for Online.xlsx

[spoonedmango]

Ohhhhh.... if I use REPT, wouldn't I have to use MAX as well?

[TMS]

OK, try this array formula:

C2: =SUM(INDEX($E2:$IV2,MATCH(TRUE,LEN(E2:IV2)<>0,0)):INDEX($E2:$IV2,MATCH(TRUE,LEN(E2:IV2)<>0,0)+ROUNDUP($B2,0)))

committed with Ctrl-Shift-Enter rather than just Enter. Then drag down.

You'll see:

{=SUM(INDEX($E2:$IV2,MATCH(TRUE,LEN(E2:IV2)<>0,0)):INDEX($E2:$IV2,MATCH(TRUE,LEN(E2:IV2)<>0,0)+ROUNDUP($B2,0)))}


Regards, TMS

[spoonedmango]

TMS,
You must be a very patient person, for which I am grateful!

I put the formula in, and it worked perfectly for the ones that before came out 0. Most of the others it doubled, the rest I haven't figured out how it got those numbers. I've attached the file.

Regards,
Spoonedmango

[TMS]

I guess it could be because you have included the column with your manual calculation:

=SUM(INDEX($D2:$IU2,MATCH(TRUE,LEN(D2:IU2)<>0,0)):INDEX($D2:$IU2,MATCH(TRUE,LEN(D2:IU2)<>0,0)+ROUNDUP($B2,0)))

Your data starts, potentially, in column E. By including your manual calculation, you have in many cases, doubled the return.

There is, however, a slight amendment:

=SUM(INDEX($E3:$IV3,MATCH(TRUE,LEN(E3:IV3)<>0,0)):INDEX($E3:$IV3,MATCH(TRUE,LEN(E3:IV3)<>0,0)+ROUNDUP($B3,0)-1)) ... the "-1" at the end.

Please see the original example with the formula in place. Most values are correct but not all. Note that I have added row 2 which gives a column number indicator. Bear in mind that this is relative to column E, not the first non blank column in the row.

[spoonedmango]

I just spoke to the person who is in charge of this little project. They told me wrong the first time around. I am so sorry to have wasted your time on the wrong thing.

Here's what I actually need: Start in column C, count over the number of days, add them together, go to column D, count over the number of days, add them together, etc.... until you have no more days. The largest sum you find, is the largest pull.

Is that even possible with a formula?

[TMS]

I'd do that on a separate sheet. Same idea using the start cell C2, whatever, and INDEX(C2:$IV2, B2) where B2 has the number of days. Drag across. Take the MAX of the values calculated.

I don't know if you could do it with one formula, but I wouldn't put a lot of effort into working it out.

Regards, TMS

[spoonedmango]

YES!!!!! You are amazing TMS!!!! Thank you so much, it worked perfectly! Now I need to learn about INDEX so I understand what I did! Lol!

Regards,
Spoonedmango

[TMS]

You're welcome. Just relieved that you have your solution ... and you're moving towards doing it on your own.

Regards, TMS