Dynamic Report

Cubist

Cubist

Well-known Member
Joined
Oct 5, 2023
Messages
2,263
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a data set with a maximum of 36 months, but can be shorter. I'm trying to create a dynamic report. The current period is the most recent 12 months, the prior period is the prior 12 months, and prior-1 is the oldest 12 months.

TIA

sample.xlsx
MNOPQRS
1DataSummary
2MonthAmountMonthCurrent PeriodPrior PeriodPrior -1 Period
312/31/21$219.80February$81.92
41/31/22$440.77March$100.91
52/28/22$81.92April$220.03
63/31/22$100.91May$473.23
74/30/22$220.03June$384.50
85/31/22$473.23July$413.65
96/30/22$384.50August$328.10
107/31/22$413.65September$390.76
118/31/22$328.10October$259.99
129/30/22$390.76November$275.12
1310/31/22$259.99December$372.04$219.80
1411/30/22$275.12January$308.25$440.77
1512/31/22372.0418
161/31/23$308.25
171/0/00#DIV/0!
181/0/00#DIV/0!
191/0/00#DIV/0!
201/0/00#DIV/0!
211/0/00#DIV/0!
221/0/00#DIV/0!
231/0/00#DIV/0!
241/0/00#DIV/0!
251/0/00#DIV/0!
261/0/00#DIV/0!
271/0/00#DIV/0!
281/0/00#DIV/0!
291/0/00#DIV/0!
301/0/00#DIV/0!
311/0/00#DIV/0!
321/0/00#DIV/0!
331/0/00#DIV/0!
341/0/00#DIV/0!
351/0/00#DIV/0!
361/0/00#DIV/0!
371/0/00#DIV/0!
381/0/00#DIV/0!
Sheet1
Cell Formulas
RangeFormula
P3:P14P3=TEXT(TAKE(FILTER(M3:M38,M3:M38>0),-12),"mmmm")
Dynamic array formulas.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
  1. Does column P have to be text? Could it instead be dates formatted to show month name?
  2. Does the order have to be as you showed? Could it instead start from the latest month & be in reverse?
23 11 09.xlsm
MNOPQRS
1DataSummary
2MonthAmountMonthCurrent PeriodPrior PeriodPrior -1 Period
331/12/2021219.80January308.25440.77
431/01/2022440.77December372.04219.80
528/02/202281.92November275.12
631/03/2022100.91October259.99
730/04/2022220.03September390.76
831/05/2022473.23August328.10
930/06/2022384.50July413.65
1031/07/2022413.65June384.50
1131/08/2022328.10May473.23
1230/09/2022390.76April220.03
1331/10/2022259.99March100.91
1430/11/2022275.12February81.92
1531/12/2022372.04
1631/01/2023308.25
170#DIV/0!
180#DIV/0!
190#DIV/0!
200#DIV/0!
210#DIV/0!
220#DIV/0!
230#DIV/0!
240#DIV/0!
250#DIV/0!
260#DIV/0!
270#DIV/0!
280#DIV/0!
290#DIV/0!
300#DIV/0!
310#DIV/0!
320#DIV/0!
330#DIV/0!
340#DIV/0!
350#DIV/0!
360#DIV/0!
370#DIV/0!
380#DIV/0!
Report
Cell Formulas
RangeFormula
P3:P14P3=LET(c,COUNTIF(M3:M38,">0"),TAKE(INDEX(M3:M38,SEQUENCE(c,,c,-1)),12))
Q3:R14Q3=LET(d,SORTBY(N3:N38,ROW(N3:N38),-1),WRAPCOLS(FILTER(d,ISNUMBER(d)),12,""))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
  1. Does column P have to be text? Could it instead be dates formatted to show month name?
  2. Does the order have to be as you showed? Could it instead start from the latest month & be in reverse?
23 11 09.xlsm
MNOPQRS
1DataSummary
2MonthAmountMonthCurrent PeriodPrior PeriodPrior -1 Period
331/12/2021219.80January308.25440.77
431/01/2022440.77December372.04219.80
528/02/202281.92November275.12
631/03/2022100.91October259.99
730/04/2022220.03September390.76
831/05/2022473.23August328.10
930/06/2022384.50July413.65
1031/07/2022413.65June384.50
1131/08/2022328.10May473.23
1230/09/2022390.76April220.03
1331/10/2022259.99March100.91
1430/11/2022275.12February81.92
1531/12/2022372.04
1631/01/2023308.25
170#DIV/0!
180#DIV/0!
190#DIV/0!
200#DIV/0!
210#DIV/0!
220#DIV/0!
230#DIV/0!
240#DIV/0!
250#DIV/0!
260#DIV/0!
270#DIV/0!
280#DIV/0!
290#DIV/0!
300#DIV/0!
310#DIV/0!
320#DIV/0!
330#DIV/0!
340#DIV/0!
350#DIV/0!
360#DIV/0!
370#DIV/0!
380#DIV/0!
Report
Cell Formulas
RangeFormula
P3:P14P3=LET(c,COUNTIF(M3:M38,">0"),TAKE(INDEX(M3:M38,SEQUENCE(c,,c,-1)),12))
Q3:R14Q3=LET(d,SORTBY(N3:N38,ROW(N3:N38),-1),WRAPCOLS(FILTER(d,ISNUMBER(d)),12,""))
Dynamic array formulas.
Click to expand...
Hi Peter,

1) It does not have to be text. Can be date formatted as month name
2) I need it in the order shown in the sample.
 
Upvote 0
I wanted to add clarity to bullet 2) above. Basically, cell P14 needs to be the latest month in the data then count backward to get the full year. In this example, it's Jan, but it could be any month.
 
Upvote 0
Cubist said:
1) It does not have to be text. Can be date formatted as month name
2) I need it in the order shown in the sample.
Click to expand...
OK, see if this works then.
Most probably an issue if 29 February turns up but have not tested for that.

23 11 09.xlsm
MNOPQRS
1DataSummary
2MonthAmountMonthCurrent PeriodPrior PeriodPrior -1 Period
331/12/2021219.80February81.92
431/01/2022440.77March100.91
528/02/202281.92April220.03
631/03/2022100.91May473.23
730/04/2022220.03June384.50
831/05/2022473.23July413.65
930/06/2022384.50August328.10
1031/07/2022413.65September390.76
1131/08/2022328.10October259.99
1230/09/2022390.76November275.12
1331/10/2022259.99December372.04219.80
1430/11/2022275.12January308.25440.77
1531/12/2022372.04
1631/01/2023308.25
170#DIV/0!
180#DIV/0!
190#DIV/0!
200#DIV/0!
210#DIV/0!
220#DIV/0!
230#DIV/0!
240#DIV/0!
250#DIV/0!
260#DIV/0!
270#DIV/0!
280#DIV/0!
290#DIV/0!
300#DIV/0!
310#DIV/0!
320#DIV/0!
330#DIV/0!
340#DIV/0!
350#DIV/0!
360#DIV/0!
370#DIV/0!
380#DIV/0!
Report
Cell Formulas
RangeFormula
P3:P14P3=LET(c,COUNTIF(M3:M38,">0"),TAKE(INDEX(M3:M38,SEQUENCE(12,,c-11)),12))
Q3:S14Q3=MAKEARRAY(12,3,LAMBDA(r,c,XLOOKUP(EDATE(INDEX(P3#,r),(1-c)*12),M3:M38,N3:N38,"")))
Dynamic array formulas.
 
Upvote 0
Solution
Peter_SSs said:
OK, see if this works then.
Most probably an issue if 29 February turns up but have not tested for that.

23 11 09.xlsm
MNOPQRS
1DataSummary
2MonthAmountMonthCurrent PeriodPrior PeriodPrior -1 Period
331/12/2021219.80February81.92
431/01/2022440.77March100.91
528/02/202281.92April220.03
631/03/2022100.91May473.23
730/04/2022220.03June384.50
831/05/2022473.23July413.65
930/06/2022384.50August328.10
1031/07/2022413.65September390.76
1131/08/2022328.10October259.99
1230/09/2022390.76November275.12
1331/10/2022259.99December372.04219.80
1430/11/2022275.12January308.25440.77
1531/12/2022372.04
1631/01/2023308.25
170#DIV/0!
180#DIV/0!
190#DIV/0!
200#DIV/0!
210#DIV/0!
220#DIV/0!
230#DIV/0!
240#DIV/0!
250#DIV/0!
260#DIV/0!
270#DIV/0!
280#DIV/0!
290#DIV/0!
300#DIV/0!
310#DIV/0!
320#DIV/0!
330#DIV/0!
340#DIV/0!
350#DIV/0!
360#DIV/0!
370#DIV/0!
380#DIV/0!
Report
Cell Formulas
RangeFormula
P3:P14P3=LET(c,COUNTIF(M3:M38,">0"),TAKE(INDEX(M3:M38,SEQUENCE(12,,c-11)),12))
Q3:S14Q3=MAKEARRAY(12,3,LAMBDA(r,c,XLOOKUP(EDATE(INDEX(P3#,r),(1-c)*12),M3:M38,N3:N38,"")))
Dynamic array formulas.
Click to expand...
Thanks, Peter. Works great. 2/29 won't be an issue because I have data validation for the data entry.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
619
awoohaw
awoohaw
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
220
StephenCrump
S
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
554
XLfriend777
XLfriend777
B
  • Solved
Extract a range of values
Replies
8
Views
533
Fluff
Fluff
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,635
Messages
6,186,130
Members
453,340
Latest member
Stu61

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top