Dynamic Report

Cubist

Cubist

Well-known Member
Joined
Oct 5, 2023
Messages
774
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
316
awoohaw
awoohaw
K
  • 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
159
XLfriend777
XLfriend777
M
  • Solved
Sumifs Formula not working
2
Replies
15
Views
821
Peter_SSs
Peter_SSs
F
  • Solved
Complex Dynamic Array Cash Flow Spread - Returns Only $0
2
Replies
16
Views
314
felixstraube
F
W
  • Question
Count periods between to dates/times (lookup)
Replies
0
Views
360
Woofy_McWoof_Woof
W

Forum statistics

Threads
1,215,110
Messages
6,123,148
Members
449,098
Latest member
Doanvanhieu

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