Populate Column D if Condition is Met

[bjnockle]

Looking for formula (non-array preferred) to look in F2:H36 and populate D2:D827 (desired output)if condition is met. To populate D2:D827, the condition to use is in B2 (B2=25).

Condition: In B2, Total Sales value is 25 (B2=25). Total Sales value (condition in B2) is manually entered. For the example in this sample file, 25 is manually entered in B2.

To populate D2:D827, the formula will look into column H for week 1 and return all week 1 dates 25 times (condition in B2 =25). For week 2 dates, the formula will look into column H and and return all week 2 dates 25 times (condition in B2 =25) right where week 1 dates ends and continue this process until the last week is populated.

See sample file.

Thanks.

[Pete_UK]

Do you need to have the seven dates for week1 populated in sequence 25 times, or could you have the first date (27/12/2020) populated on the first 25 rows, then the next date in the next 25 rows, and so on?

Pete

[bjnockle]

Pete_UK: Do you need to have the seven dates for week1 populated in sequence 25 times. Yes, I need to have the seven dates for week1 populated in sequence 25 times and then the next seven dates for week 2 populated in sequence 25 times and so on. Thanks

[Pete_UK]

You can use this formula in cell D2 (actually, I put it in E2 for comparison with your results):

=INDEX(F:F,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)

Then copy it down as far as you need to. If you have 5 weeks of 7 dates and you want to repeat them 25 times, this will mean that you need 5 * 7 * 25 cells (i.e. 875), so copy the formula down to row 876 (as it starts in row 2). Your list seems to have gone a bit awry.

If the number in B1 might vary and you want blanks to be returned once all the data has been reproduced, then you could amend the formula to this:

=IFERROR(1/(1/INDEX(F:F,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)),"")

and then you can copy the formula down as far as you like.

Hope this helps.

Pete

[bjnockle]

Pete_UK: The proposed formula works like a charm. Please assist with a formula to have seven dates for week1 populate Week1, Week2, Week3 and so on in sequence 25 times in column C. See sample file. Thanks a lot.

[Bo_Ry]

Please try at
C2
="week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1

D2
=MOD(ROWS(D$2:D2)-1,7)+INT((ROWS(D$2:D2)-1)/7/$B$1)*7+$F$2

[Pete_UK]

... Please assist with a formula to have seven dates for week1 populate Week1, Week2, Week3 and so on ...

Bo_Ry beat me to it, though you might like to amend his formula in C2 to this:

=IF(D2="","","week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1)

then you can copy that down as far as you like and get blanks beyond the final value.

Hope this helps.

Pete

[Pete_UK]

By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[bjnockle]

Pete_UK: Outstanding solution. Thanks a lot.

[bjnockle]

Outstanding solution, Bo_Ry.

[bjnockle]

Pete_UK: This proposed formula (=IF(D2="","","week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1) ) works for column F2:F36 (month = Jan). Would like the formula adjusted to repeat week 1, week 2, week 3, week 4....... for the month of Feb (month in column I), same for Mar and so on. Desired outcome in column C. See sample file.

Adjustment needed:
I adjusted/modified your proposed formula =IFERROR(1/(1/INDEX(F:F,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)),"") to =IFERROR(1/(1/INDEX($F$2:$F$92,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)),""). The one I modified/adjusted is not pulling correctly (in column J).

Please assist in fixing this formula in column J to pull exactly like your proposed formula in column D (column D formula working perfectly). See attached file.

Thanks.

[JohnTopley]

Guessing ..

in J2

=IFERROR(1/(1/INDEX($F$36:$F$10000,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)),"")

in K2

=IFERROR(1/(1/INDEX($F$64:$F$10000,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)),"")

[Pete_UK]

It is easier just to look it up from column H. Put this formula in C2:

=INDEX(H:H,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)&""

then copy down as far as you like.

Hope this helps.

Pete

[Pete_UK]

Please note that in your formula in J2 you have changed the range so that it starts from the second cell - consequently, you will need +1 rather than +2 towards the end of the formula.

Hope this helps.

Pete

[bjnockle]

Pete_UK: It is easier just to look it up from column H. Column H is an example of the desired outcome I am looking for in column C. I manually entered the data in column H as example of what the desired outcome should be in column C. The proposed INDEX(H:H,INT((ROWS($1:1)-1)/($B$1*7))*7 + MOD(ROWS($1:1)-1,7)+2)&"" is looking into column H instead of D. The first proposed formula =IF(D2="","","week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1) needs tweaking to address the issue. Please help fix.

[JohnTopley]

Did you look at post #12?

[bjnockle]

JohnTopley:Did you look at post #12?. I did look at post 12. Proposed answer in post 12 is not the correct solution. The solution I am looking for in column C will need to build on Pete_UK proposed formula =IF(D2="","","week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1). Thanks.

[JohnTopley]

Formula in C2 is CONSTANT and simply lists repetitive number of "Week n" .(Date independent)

The formula in D is Date dependent.

in F2

31/01/2021

if F3=F2+1

Copy down

[bjnockle]

JohnTopley: Column F is my sample data. The only column needing formula is column C. Please see attached

Pete_UK: This proposed formula (=IF(D2="","","week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1) ) works for column F2:F36 (month = Jan). Would like the formula adjusted to repeat week 1, week 2, week 3, week 4....... for the month of Feb (month in column I), same for Mar and so on. Desired outcome in column C. See sample file.

[JohnTopley]

Why are you reluctant to use column H as proposed by Pete? For starters you would have to provide the starting date of "Week 1" for each month { [and perhaps how many weeks in that month (the 5 4,4)

As the data is already calculated why not use it? Treat column H as "helper" column and keep things simple.

Based on using H

in C2 and copied down

=INDEX($H:$H,MATCH($D2,$F:$F,1))

Just as F3 could be

=F2+1

and copied down

[bjnockle]

JohnTopley: Your proposed formula should look into E2:E2276 and return week 1, week 2, week 3, and so on and not column H. I have deleted column H to eliminate any confusion. The initial formula proposed by Bo_Ry Please try at
C2
="week "&INT((ROWS(D$2:D2)-1)/7/$B$1)+1 works except that the formula is not repeating the week numbers as desired. See attached sample file.

Thanks

[JohnTopley]

I am not going to waste any more of my time when you take such an attitude re using the former column H as a helper column. I know why the formula does not work .....

[bjnockle]

JohnTopley: I apologize if you feel I take such an attitude. I have not taken an attitude. I was only trying to point out that I manually entered column H to show exactly what the proposed formula should be returning. Hopefully this help clear things up. Thanks.

[JohnTopley]

That is not the issue. There was no need to remove column H when it is quite a complex formula to work out where the weeks and start and end for a given month

You had a solution which was a few minutes of your time and you expect us to spend much more of our time for what benefit???

If the column H had been derived by formula (as a helper) you would probably have accepted it (??) ! And that is the best solution anyway so we end up where we started.

Q.E.D

See attached

[bjnockle]

JohnTopley: Oustanding solution. Thanks a lot for the time spent on this and the understanding. Appreciate, JohnTopley.