Find the first number after the last zero in a column

[flarunner]

I would appreciate any and all help on the following:

I have three columns:
Column B has dates from 1/1 to 12/31.
Column C has weekdays from Column B.
Column D has both numbers and blank cells from formulas referencing another sheet.

Column D is sectioned off weekly, meaning that every Sunday is a blank cell.
Column D has zeroes to the last date of the year.

Here’s an example:

B C D
7/10 Fri 5
7/11 Sat 10
7/12 Sun
7/13 Mon 5
7/14 Tue 6
7/15 Wed 8
7/16 Thu 6
7/17 Fri 0
7/18 Sat 10
7/19 Sun
7/20 Mon 5

I would like to find the first number greater than zero immediately after the last zero in Column D not counting the last cell in the column, and return the corresponding date from Column B.

I am not looking for the last zero, which would correspond to 12/31.

So for the above example, I would like to have 7/18 as the answer.

Thanks all.

[TMS]

One way, with a Helper column ...

	A	B	C	D	E	F	G
1				Help		7/28	=INDEX(A:A,MATCH(MAX(D:D),D:D,0)+1)
2	7/10	Fri	5	0	=IF(AND(C2<>"",C2=0),MAX($D$1:D1)+1,0)
3	7/11	Sat	10	0
4	7/12	Sun		0
5	7/13	Mon	5	0
6	7/14	Tue	6	0
7	7/15	Wed	8	0
8	7/16	Thu	6	0
9	7/17	Fri	0	1
10	7/18	Sat	10	0
11	7/19	Sun		0
12	7/20	Mon	5	0
13	7/10	Fri	5	0
14	7/11	Sat	10	0
15	7/12	Sun		0
16	7/13	Mon	5	0
17	7/14	Tue	6	0
18	7/15	Wed	8	0
19	7/16	Thu	6	0
20	7/17	Fri	0	2
21	7/28	Sat	10	0
22	7/19	Sun		0
23	7/20	Mon	5	0

Regards, TMS

[alansidman]

Try this VBA solution

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[flarunner]

Alan,
Thank you for your help, but I'm trying to stay away from macros.

TMS,
Thank you also. The first helper column is wonderful. Although sadly, 7/17 is the 5th occurrence of a zero.

Because I have a formula in each cell in Column D (your Column C), excluding Sundays, I have a bunch of zeroes through the rest of the column. This makes your INDEX/MATCH formula unworkable.

If it would help to insert my Excel sheet let me know, and I'll do that.

Thanks again.

[alansidman]

Alan,
Thank you for your help, but I'm trying to stay away from macros.

Any particular reason for this action to avoid VBA? Always curious to understand what motivates this type of avoidance.

[Jonmo1]

Try

=LOOKUP(2,1/((D1:D10=0)*(D1:D10<>"")),B2:B11)

Notice the 1 row offset between the two ranges D1:D10 and B2:B11

[flarunner]

Any particular reason for this action to avoid VBA? Always curious to understand what motivates this type of avoidance.

In my tiny, little mind VBA is long and complex, with a lot of room for error. The reality might be very different, but I have had a hard time getting my head around the language.

[flarunner]

I've attached an example worksheet. In it I have the 3 pertinent columns of data, and their placement on the worksheet.

I hope this helps you guys.

[Jonmo1]

Did you try the formula I suggested?

What is your expected result in that book you posted?

[flarunner]

Did you try the formula I suggested?

What is your expected result in that book you posted?

Yes I did, and I got a 0, which is why I posted the excel sheet. I couldn't get it to work within the columns and rows I have set up.

The result I'm looking for from that sheet would be the date of 7/29.

Thanks.

[Jonmo1]

Try this one, again, notice the 1 row offsets between the ranges.

=LOOKUP(2,1/((D3:D369=0)*(D3:D369<>"")*(D4:D370>0)),B4:B370)

[flarunner]

Oh Jonmo1: You Rock!

It works perfectly!

Adding to your Rep. Changing Topic to SOLVED. And hoping I don't have any more zeroes for the rest of the year.

[Jonmo1]

You're welcome.

[alansidman]

FYI:

Thanks for your response on VBA. As a point of interest, here is how to install. Once installed, if you step through the code, F8 on the debug menu, will help you understand what is happening.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button