Repeating Rows based on Property IDs and GL Codes

[TheMacroNoob]

Hello all,

This is a difficult question for me to phrase, but I can provide background:

I have 200 properties.

I have a Statement of Cash Flows with each property listed with hundreds of GL Codes and amounts.

I want to summarize the data in a new table by taking whatever GL Codes I want from every property and their respective amounts.

I want the table to autopopulate, repeating x number of properties, with x being the count of GL Codes being asked for (in the array).

Please see attached tables for reference and explanations:

Source Data (partial):

Problem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	All data was made up with RANDARRAY and RANDBETWEEN
2			2019 Cash Flow Statement
3			Properties
4		GL Code	110	112	113	115	121	124	133	134	144	149	165	166	167	175	179	183	185
5		9018 - 3975	$ 179,075	$ 184,663	$ 363,728	$ 1,473,317	$ 817,113	$ 234,652	$ 2,167,385	$ 2,984,864	$ 2,476,027	$ 958,822	$ 1,125,722	$ 948,234	$ 2,138,838	$ 1,318,665	$ 2,234,815	$ 1,588,321	$ 2,899,993
6		3687 - 4051	$ 2,455,947	$ 2,321	$ 96,571	$ 2,439,016	$ 141,627	$ 2,488,768	$ 2,665,904	$ 3,557,834	$ 1,071,210	$ 1,684,427	$ 3,378,072	$ 2,210,241	$ 1,984,218	$ 1,392,449	$ 3,304,942	$ 734,681	$ 2,578,977
7		6156 - 2110	$ 3,157,994	$ 3,221,658	$ 1,753,050	$ 3,608,577	$ 1,423,741	$ 2,551,162	$ 1,532,512	$ 2,606,249	$ 1,592,039	$ 228,352	$ 1,071,812	$ 428,550	$ 361,410	$ 1,255,680	$ 495,482	$ 2,312,292	$ 596,899
8		3550 - 8125	$ 1,088,581	$ 2,033,878	$ 3,127,298	$ 1,086,165	$ 3,168,640	$ 1,512,111	$ 2,620,671	$ 2,354,313	$ 3,001,896	$ 3,406,912	$ 1,289,037	$ 1,071,144	$ 736,989	$ 2,952,295	$ 235,759	$ 1,001,933	$ 3,416,887
9		8404 - 4835	$ 1,966,240	$ 3,313,704	$ 2,232,874	$ 3,728,413	$ 220,823	$ 1,244,356	$ 335,829	$ 2,932,846	$ 2,157,596	$ 3,065,124	$ 254,841	$ 546,821	$ 2,345,589	$ 2,102,341	$ 1,881,300	$ 1,707,392	$ 774,533
10		7243 - 437	$ 3,103,540	$ 266,340	$ 2,551,253	$ 1,216,340	$ 2,899,906	$ 572,478	$ 3,721,425	$ 2,515,094	$ 1,878,005	$ 2,141,378	$ 1,837,767	$ 2,444,706	$ 180,482	$ 411,546	$ 2,168,999	$ 2,526,265	$ 367,847
11		5221 - 1341	$ 907,666	$ 983,333	$ 987,782	$ 3,117,649	$ 220,217	$ 1,327,444	$ 3,213,442	$ 3,058,870	$ 1,480,100	$ 1,070,681	$ 1,503,279	$ 2,256,452	$ 2,025,013	$ 2,434,049	$ 538,272	$ 1,638,792	$ 496,171
12		1169 - 7299	$ 3,091,847	$ 2,231,718	$ 217,002	$ 755,961	$ 238,103	$ 1,487,689	$ 984,466	$ 165,172	$ 3,487,859	$ 3,536,388	$ 451,487	$ 515,392	$ 3,602,264	$ 1,273,034	$ 3,391,294	$ 2,425,122	$ 2,255,299
13		5814 - 6472	$ 3,722,888	$ 3,559,518	$ 2,802,230	$ 2,270,267	$ 2,236,445	$ 2,941,433	$ 409,623	$ 2,225,633	$ 2,004,412	$ 654,217	$ 112,213	$ 3,048,675	$ 1,326,038	$ 2,737,283	$ 1,473,678	$ 38,281	$ 1,848,417
14		2600 - 7438	$ 507,834	$ 1,232,269	$ 677,183	$ 1,646,061	$ 1,665,917	$ 2,675,219	$ 3,410,790	$ 1,003,430	$ 3,496,876	$ 1,903,403	$ 1,830,014	$ 452,334	$ 1,466,389	$ 3,404,281	$ 3,368,917	$ 854,423	$ 3,521,404
15		5114 - 7209	$ 717,448	$ 2,125,775	$ 1,969,333	$ 2,167,924	$ 675,878	$ 1,515,178	$ 2,012,437	$ 2,979,901	$ 1,288,748	$ 3,132,091	$ 1,350,874	$ 951,877	$ 38,464	$ 2,728,831	$ 969,236	$ 1,433,706	$ 2,647,307
16		5603 - 5346	$ 2,589,287	$ 586,038	$ 291,287	$ 2,802,585	$ 2,753,681	$ 1,836,617	$ 2,465,490	$ 728,678	$ 319,918	$ 1,048,666	$ 1,949,913	$ 835,096	$ 2,342,585	$ 1,002,010	$ 1,318,916	$ 1,408,069	$ 1,832,834
17		7975 - 6222	$ 1,905,692	$ 1,780,824	$ 3,112,710	$ 1,853,111	$ 2,838,728	$ 3,299,346	$ 2,383,128	$ 1,583,299	$ 1,781,832	$ 3,120,526	$ 3,567,861	$ 106,926	$ 2,303,937	$ 3,456,236	$ 3,257,131	$ 2,078,590	$ 2,793,481
18		1387 - 876	$ 3,274,210	$ 841,542	$ 1,612,069	$ 604,407	$ 497,067	$ 1,727,911	$ 2,180,380	$ 3,755,065	$ 2,963,279	$ 990,518	$ 1,348,414	$ 3,153,814	$ 3,593,566	$ 1,027,178	$ 1,486,690	$ 565,919	$ 3,722,060
19		5563 - 3221	$ 1,397,984	$ 23,582	$ 200,058	$ 1,799,612	$ 620,012	$ 270,237	$ 1,562,031	$ 349,536	$ 3,652,216	$ 3,701,646	$ 1,151,300	$ 2,347,915	$ 620,433	$ 1,918,702	$ 1,397,234	$ 1,803,296	$ 2,351,267
20		6551 - 6845	$ 430,567	$ 2,511,868	$ 2,640,184	$ 3,153,600	$ 201,407	$ 2,599,292	$ 3,516,213	$ 522,423	$ 2,201,644	$ 3,185,571	$ 476,425	$ 1,845,233	$ 376,476	$ 1,109,170	$ 1,270,072	$ 734,781	$ 76,748
21		8290 - 133	$ 3,516,160	$ 1,162,917	$ 199,082	$ 3,212,881	$ 2,876,746	$ 2,677,231	$ 1,927,155	$ 1,549,328	$ 30,814	$ 3,180,608	$ 2,399,809	$ 1,224,171	$ 2,759,404	$ 2,194,486	$ 1,719,355	$ 842,940	$ 259,111
22		9068 - 9522	$ 1,812,470	$ 2,538,650	$ 168,588	$ 209,519	$ 2,903,103	$ 2,793,371	$ 541,420	$ 2,438,212	$ 391,260	$ 48,195	$ 2,018,408	$ 366,581	$ 3,549,227	$ 654,167	$ 3,714,558	$ 2,505,645	$ 419,353
23		5582 - 9136	$ 2,899,735	$ 360,318	$ 2,895,087	$ 923,171	$ 3,179,058	$ 355,244	$ 1,411,660	$ 292,495	$ 1,320,465	$ 2,458,001	$ 3,034,002	$ 2,614,947	$ 3,259,089	$ 3,270,317	$ 3,561,878	$ 1,901,450	$ 3,233,096
24		9576 - 8574	$ 3,213,617	$ 646,481	$ 2,473,931	$ 3,013,120	$ 1,442,658	$ 3,351,702	$ 25,892	$ 917,942	$ 3,109,824	$ 1,430,827	$ 1,605,122	$ 3,670,433	$ 1,667,915	$ 847,314	$ 1,656,341	$ 1,934,427	$ 2,559,716
25		3392 - 227	$ 1,009,223	$ 336,451	$ 567,596	$ 1,975,252	$ 1,062,357	$ 427,422	$ 3,612,963	$ 34,719	$ 2,964,854	$ 1,858,046	$ 2,110,025	$ 1,787,909	$ 3,082,262	$ 845,960	$ 3,643,728	$ 2,582,891	$ 1,828,786
26		5005 - 9949	$ 651,023	$ 1,282,474	$ 1,821,617	$ 2,250,265	$ 2,502,081	$ 865,839	$ 3,532,027	$ 1,688,567	$ 1,408,048	$ 2,235,434	$ 3,015,132	$ 358,157	$ 3,593,291	$ 2,757,425	$ 1,132,302	$ 605,043	$ 3,192,015
27		2082 - 6163	$ 2,993,938	$ 2,076,900	$ 2,448,867	$ 1,511,706	$ 3,515,801	$ 315,222	$ 2,735,208	$ 2,584,599	$ 1,239,040	$ 90,225	$ 1,598,037	$ 671,175	$ 250,770	$ 1,924,917	$ 1,497,116	$ 3,715,407	$ 194,322
28		4411 - 6635	$ 1,007,257	$ 2,240,256	$ 1,357,340	$ 2,918,954	$ 1,759,764	$ 2,746,147	$ 3,532,311	$ 2,827,579	$ 3,690,861	$ 1,673,700	$ 2,321,598	$ 3,326,566	$ 210,867	$ 3,263,419	$ 2,106,135	$ 1,108,008	$ 2,876,238
29		6037 - 4583	$ 2,896,206	$ 1,948,263	$ 2,685,926	$ 539,561	$ 1,475,877	$ 2,507,585	$ 2,299,527	$ 1,579,871	$ 1,214,320	$ 1,588,200	$ 472,244	$ 1,452,640	$ 3,726,505	$ 2,946,044	$ 3,448,224	$ 3,407,430	$ 3,683,909
30		9298 - 9458	$ 3,135,775	$ 320,725	$ 2,030,337	$ 2,776,067	$ 1,977,524	$ 2,722,467	$ 233,869	$ 2,812,629	$ 186,194	$ 163,865	$ 2,160,825	$ 367,211	$ 1,478,373	$ 1,941,277	$ 1,889,556	$ 1,313,336	$ 3,698,534
31		1304 - 961	$ 2,720,536	$ 1,700,664	$ 1,711,794	$ 2,975,813	$ 2,785,485	$ 1,417,477	$ 832,099	$ 392,146	$ 3,358,989	$ 2,043,246	$ 1,956,715	$ 2,387,672	$ 1,935,471	$ 3,084,915	$ 2,522,865	$ 2,317,956	$ 3,553,918
32		5013 - 6229	$ 2,039,824	$ 1,749,217	$ 2,349,640	$ 1,291,492	$ 2,992,140	$ 2,129,148	$ 2,575,738	$ 2,320,535	$ 3,434,253	$ 674,056	$ 1,137,071	$ 3,045,517	$ 3,049,781	$ 2,779,959	$ 965,741	$ 186,619	$ 1,043,605
Input 2019

Desired Result:

Problem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3		2020 and 2021 data is made up, more input sheets would exist
4		Desired Result:								The Problem:
5
6		Property	GL Code	2019	2020	2021				I want to form a data table to fill in each property's amount of each of the specified GL Codes for 3 years:
7		110	3550 - 8125	$ 1,088,581	$ 1,083,730	$ 602,250				GL Codes (User Input):
8		110	2600 - 7438	$ 507,834	$ 502,983	$ 21,503				3550 - 8125
9		110	7975 - 6222	$ 1,905,692	$ 1,900,841	$ 1,419,361				2600 - 7438
10		110	9576 - 8574	$ 3,213,617	$ 3,208,766	$ 2,727,286				7975 - 6222
11		110	9134 - 9138	$ 3,224,719	$ 3,219,868	$ 2,738,388				9576 - 8574
12		110	2743 - 8957	$ 2,277,437	$ 2,272,586	$ 1,791,106				9134 - 9138
13		110	4019 - 566	$ 862,358	$ 857,507	$ 376,027				2743 - 8957
14		110	4078 - 9237	$ 3,206,562	$ 3,201,711	$ 2,720,231				4019 - 566
15		110	6227 - 5922	$ 3,121,344	$ 3,116,493	$ 2,635,013				4078 - 9237
16		110	7973 - 9827	$ 1,023,821	$ 1,018,970	$ 537,490				6227 - 5922
17		110	6075 - 9451	$ 2,461,873	$ 2,457,022	$ 1,975,542				7973 - 9827
18		110	5186 - 3373	$ 1,935,359	$ 1,930,508	$ 1,449,028				6075 - 9451
19		112	3550 - 8125	$ 2,033,878	$ 2,029,027	$ 1,547,547				5186 - 3373
20		112	2600 - 7438	$ 1,232,269	$ 1,227,418	$ 745,938
21		112	7975 - 6222	$ 1,780,824	$ 1,775,973	$ 1,294,493				In the "Property" column:
22		112	9576 - 8574	$ 646,481	$ 641,630	$ 160,150				Ideally, I would have a formula to autopopulate the property field to repeat x amount of times, x being the count of GL codes I need.
23		112	9134 - 9138	$ 3,247,466	$ 3,242,615	$ 2,761,135				The array would fill in one single column, repeating each property 8 times if there are 8 GL Codes asked for
24		112	2743 - 8957	$ 840,503	$ 835,652	$ 354,172
25		112	4019 - 566	$ 2,483,909	$ 2,479,058	$ 1,997,578				In the "GL Code" column:
26		112	4078 - 9237	$ 2,262,658	$ 2,257,807	$ 1,776,327				A simple formula to autopopulate the array shown above in a single column. If there are 8 GL codes, I'd like those GL codes repeated once per property.
27		112	6227 - 5922	$ 75,733	$ 70,882	$ (410,598)
28		112	7973 - 9827	$ 1,530,004	$ 1,525,153	$ 1,043,673				In the "2019" (and "2020", "2021") column:
29		112	6075 - 9451	$ 2,066,705	$ 2,061,854	$ 1,580,374				I would use a nested XLOOKUP to find the dollar amount for that property and the GL code.
30		112	5186 - 3373	$ 1,474,791	$ 1,469,940	$ 988,460
31		113	3550 - 8125	$ 3,127,298	$ 3,122,447	$ 2,640,967
32		113	2600 - 7438	$ 677,183	$ 672,332	$ 190,852
33		113	7975 - 6222	$ 3,112,710	$ 3,107,859	$ 2,626,379
34		113	9576 - 8574	$ 2,473,931	$ 2,469,080	$ 1,987,600
35		113	9134 - 9138	$ 1,256,887	$ 1,252,036	$ 770,556
36		113	2743 - 8957	$ 2,680,288	$ 2,675,437	$ 2,193,957
37		113	4019 - 566	$ 329,871	$ 325,020	$ (156,460)
38		113	4078 - 9237	$ 2,240,165	$ 2,235,314	$ 1,753,834
39		113	6227 - 5922	$ 1,156,202	$ 1,151,351	$ 669,871
40		113	7973 - 9827	$ 793,362	$ 788,511	$ 307,031
41		113	6075 - 9451	$ 73,356	$ 68,505	$ (412,975)
42		113	5186 - 3373	$ 2,052,948	$ 2,048,097	$ 1,566,617
43		And so on….
Output

Cell Formulas
Range	Formula
D7:D42	D7	=XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))
E7:E42	E7	=536-5387+XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))
F7:F42	F7	=53656-539987+XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))

Named Ranges
Name	Refers To	Cells
GLCodes	='Input 2019'!$B$5:$B$123	D7:F42
Properties	='Input 2019'!$C$4:$GT$4	D7:F42

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Repeating Rows based on Property IDs and GL Codes
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

How about for the 1st two columns

=LET(rng,T8:T19,r,ROWS(rng),s,SEQUENCE(r*COLUMNS(Properties),,0),CHOOSE({1,2},INDEX(Properties,INT(s/r)+1),INDEX(rng,MOD(s,r)+1)))

 

[TheMacroNoob]

How about for the 1st two columns

=LET(rng,T8:T19,r,ROWS(rng),s,SEQUENCE(r*COLUMNS(Properties),,0),CHOOSE({1,2},INDEX(Properties,INT(s/r)+1),INDEX(rng,MOD(s,r)+1)))

Hi Fluff,

Thank you so much for your help. The function definitely works as intended, although I have much to learn to understand the formula elements. Is there a way to filter the overall formula so that the number of times the properties repeat updates dynamically based on the number of cells in the initial (T8:T19) range? As it stands now it will repeat the property IDs as many times as there are cells within the range.

IF you don't know the solution for that, I thank you for what you have provided, this is excellent learning material.

 

[Fluff]

Are the codes in T8:T19 being input manually?

 

[TheMacroNoob]

Are the codes in T8:T19 being input manually?

Yes, they would be manually input. For my purposes I would extend the range to accommodate more potential codes.

 

[TheMacroNoob]

In my original follow up reply, I should have said "is there a way to filter the overall formula so that the number of times the properties repeat updates dynamically based on the number of cells WITH VALUES (Ignoring empty cells) in the initial (T8:T19) range?

 

[Fluff]

How about

=LET(rng,FILTER(T8:T19,T8:T19<>""),r,ROWS(rng),s,SEQUENCE(r*COLUMNS(Properties),,0),CHOOSE({1,2},INDEX(Properties,INT(s/r)+1),INDEX(rng,MOD(s,r)+1)))

 

[TheMacroNoob]

How about

=LET(rng,FILTER(T8:T19,T8:T19<>""),r,ROWS(rng),s,SEQUENCE(r*COLUMNS(Properties),,0),CHOOSE({1,2},INDEX(Properties,INT(s/r)+1),INDEX(rng,MOD(s,r)+1)))

That works perfectly. You are absolutely brilliant. I aspire to be as good as you at excel! I was having a difficult time with this formula until I realized my new "Properties" array was transposed into rows, so I changed r*COLUMNS(Properties) to r*ROWS(Properties) and voila, problem solved.

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback.