Return latest result within week cycle

[Marklarbear]

Hi wizards - hopefully this makes sense....

Below is a sample set of a process that I need my team to follow. We run on a 5 week cycle and record the numbers of products sold within a particular week in the cycle (range A2:B16). What I want is a formula in the 2nd table (range A21:B25) that returns the most up to date product sold for that week within the cycle - so it automatically updates the product sold. formulas to go in cells B21 down... hope that makes sense....

 

[SunnyAlv]

Try this

=LET(a,LET(Mark,FILTER($B$2:$B$16,$A$2:$A$16=A21),FILTER(Mark,Mark<>"","")),INDEX(a,COUNTA(a)))

 

[Peter_SSs]

Or this

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4	57
6	5	65
7	1	23
8	2	19
9	3	238
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	23
22	2	19
23	3	238
24	4	57
25	5	65
Latest

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>"")),-1)

If you want it to apply when all weeks may not yet have a value, perhaps this slight modification.

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4
6	5
7	1
8	2
9	3
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	56
22	2	254
23	3	1
24	4	N/A
25	5	N/A
Latest (2)

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>""),"N/A"),-1)

 

[Marklarbear]

Or this

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4	57
6	5	65
7	1	23
8	2	19
9	3	238
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	23
22	2	19
23	3	238
24	4	57
25	5	65
Latest

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>"")),-1)

If you want it to apply when all weeks may not yet have a value, perhaps this slight modification.

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4
6	5
7	1
8	2
9	3
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	56
22	2	254
23	3	1
24	4	N/A
25	5	N/A
Latest (2)

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>""),"N/A"),-1)

I used the slightly modified version - works brilliantly!!!!

 

[Marklarbear]

I used the slightly modified version - works brilliantly!!!!

mmm.. one of my guys 'broke' the logic.... as an example - in cell B8 the value of 0 was entered... can the formula be modified to ignore a '0' entry and return the previous value for that week ? in this case it would return 254 for week 2 instead of 0.

Hope that makes sense...

 

[Marklarbear]

Or this

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4	57
6	5	65
7	1	23
8	2	19
9	3	238
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	23
22	2	19
23	3	238
24	4	57
25	5	65
Latest

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>"")),-1)

If you want it to apply when all weeks may not yet have a value, perhaps this slight modification.

24 01 31.xlsm
	A	B
1	Week	Volume
2	1	56
3	2	254
4	3	1
5	4
6	5
7	1
8	2
9	3
10	4
11	5
12	1
13	2
14	3
15	4
16	5
17
18
19
20
21	1	56
22	2	254
23	3	1
24	4	N/A
25	5	N/A
Latest (2)

Cell Formulas
Range	Formula
B21:B25	B21	=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>""),"N/A"),-1)

mmm.. one of my guys 'broke' the logic.... as an example - in cell B8 the value of 0 was entered... can the formula be modified to ignore a '0' entry and return the previous value for that week ? in this case it would return 254 for week 2 instead of 0.

Hope that makes sense...

 

[Peter_SSs]

Assuming that means any zero entry is not to be counted then try this.

=TAKE(FILTER(B$2:B$16,(A$2:A$16=A21)*(B$2:B$16<>0),"N/A"),-1)

 

[Marklarbear]

small change - works perfectly - thankyou!

 

[Peter_SSs]

Cheers. Thanks for the confirmation.