Excel function that lists only non-zero/non-blank values in a column starting from a specific reference in a row/column

[haruspication]

I need to include a function in my C2 cell formula that will skip days with non-zero values and then list the next non-zero day (the day is also listed N times based on Col B value).

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Mon
[/TD]
[TD]2
[/TD]
[TD]Mon
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tues
[/TD]
[TD]1
[/TD]
[TD]Mon
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Wed
[/TD]
[TD]0
[/TD]
[TD]Tues
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Thurs
[/TD]
[TD]0
[/TD]
[TD]Fri
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Fri
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Cell C1: Formula finds the first day (Col A) with a non-zero value (Col B) and lists it in C1.

Cell C2: Lists each day (Col A) N times based on the day's corresponding value in Col B.

=IFERROR(IF(COUNTIF($G$2:G2,G2)=INDEX(E:E,MATCH(G2,A:A,0)),IF(AND(INDEX(A:A,MATCH(G2,A:A,0)+1)<>0,INDEX(E:E,MATCH(G2,A:A,0)+1)<>0), INDEX(A:A,MATCH(G2,A:A,0)+1), IF(INDEX(E:E,MATCH(G2,A:A,0)+1)=0,INDEX(A:A,MATCH(TRUE,E:E<>0,0)), "-")),G2),"-")

The current function to skip zeros is in RED and it's wrong because it lists "Mon" repeatedly because it only finds the first day without a zero, rather than the first day without a zero based on the previous value (eg., if the last non-zero day listed in Col C is Tues, then the formula would skip Wed and Thurs and then list Fri)

 

[haruspication]

Correction. The original comment should state "I need to include a function in my C2 cell formula that will skip days with zero values and then list the next non-zero day (the day is also listed N times based on Col B value).

 

[XOR LX]

Hi.

Assuming your table is in A1:B5, in C1, array formula**:

=IF(ROWS($1:1)>SUM(B$1:B$5),"",INDEX(A$1:A$5,MATCH(TRUE,MMULT(0+(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))),B$1:B$5)>=ROWS($1:1),0)))

Copy down as required.

Obviously amend the range as required, though be aware that, since this is an array formula, you would be strongly advised to keep the ranges referenced to a minimum size (and certainly don't reference entire columns).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[haruspication]

Hi.

Assuming your table is in A1:B5, in C1, array formula**:

=IF(ROWS($1:1)>SUM(B$1:B$5),"",INDEX(A$1:A$5,MATCH(TRUE,MMULT(0+(ROW(A$1:A$5)>=TRANSPOSE(ROW(A$1:A$5))),B$1:B$5)>=ROWS($1:1),0)))

Copy down as required.

Obviously amend the range as required, though be aware that, since this is an array formula, you would be strongly advised to keep the ranges referenced to a minimum size (and certainly don't reference entire columns).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Incredible. Thank you.This is an excellent formula with fewer steps than I could have created in my original. I have a fixed range that includes approximately 75 rows of data, so hopefully this does not slow down the formula too much.

I have never used MMULT or TRANSPOSE functions. Perhaps TRANSPOSE will come in handy when I create a new column with a formula to match each of Col C's listed days with a corresponding nonblank date found across other columns (in my actual worksheet there are multiple columns between COL A and COL B with Dates and COL B just represents the "COUNT" of dates across the row. So, if there are 2 Monday's listed in COL C, that's because there were 2 dates listed across the 5 possible columns of dates. So, I will try to match each of those dates with the COL C list). Well, I'll definitely learn more about these functions' uses.

Thank you so much. I know there are other options such as VBA or pivot tables, but for this small table, where I have other formulas to calculate dates/times, etc., your formula gives me much quicker flexibility with my data.

Thanks again!

 

[XOR LX]

You're welcome! Thanks for the detailed feedback.

Regards