TheMacroNoob

Board Regular
Joined
Aug 5, 2022
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRS
1All data was made up with RANDARRAY and RANDBETWEEN
22019 Cash Flow Statement
3Properties
4GL Code110112113115121124133134144149165166167175179183185
59018 - 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
63687 - 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
76156 - 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
83550 - 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
98404 - 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
107243 - 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
115221 - 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
121169 - 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
135814 - 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
142600 - 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
155114 - 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
165603 - 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
177975 - 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
181387 - 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
195563 - 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
206551 - 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
218290 - 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
229068 - 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
235582 - 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
249576 - 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
253392 - 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
265005 - 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
272082 - 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
284411 - 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
296037 - 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
309298 - 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
311304 - 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
325013 - 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
ABCDEFGHIJKLMNOPQRSTUVWX
1
2
32020 and 2021 data is made up, more input sheets would exist
4Desired Result:The Problem:
5
6PropertyGL Code201920202021I want to form a data table to fill in each property's amount of each of the specified GL Codes for 3 years:
71103550 - 8125$ 1,088,581$ 1,083,730$ 602,250GL Codes (User Input):
81102600 - 7438$ 507,834$ 502,983$ 21,5033550 - 8125
91107975 - 6222$ 1,905,692$ 1,900,841$ 1,419,3612600 - 7438
101109576 - 8574$ 3,213,617$ 3,208,766$ 2,727,2867975 - 6222
111109134 - 9138$ 3,224,719$ 3,219,868$ 2,738,3889576 - 8574
121102743 - 8957$ 2,277,437$ 2,272,586$ 1,791,1069134 - 9138
131104019 - 566$ 862,358$ 857,507$ 376,0272743 - 8957
141104078 - 9237$ 3,206,562$ 3,201,711$ 2,720,2314019 - 566
151106227 - 5922$ 3,121,344$ 3,116,493$ 2,635,0134078 - 9237
161107973 - 9827$ 1,023,821$ 1,018,970$ 537,4906227 - 5922
171106075 - 9451$ 2,461,873$ 2,457,022$ 1,975,5427973 - 9827
181105186 - 3373$ 1,935,359$ 1,930,508$ 1,449,0286075 - 9451
191123550 - 8125$ 2,033,878$ 2,029,027$ 1,547,5475186 - 3373
201122600 - 7438$ 1,232,269$ 1,227,418$ 745,938
211127975 - 6222$ 1,780,824$ 1,775,973$ 1,294,493In the "Property" column:
221129576 - 8574$ 646,481$ 641,630$ 160,150Ideally, 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.
231129134 - 9138$ 3,247,466$ 3,242,615$ 2,761,135The array would fill in one single column, repeating each property 8 times if there are 8 GL Codes asked for
241122743 - 8957$ 840,503$ 835,652$ 354,172
251124019 - 566$ 2,483,909$ 2,479,058$ 1,997,578In the "GL Code" column:
261124078 - 9237$ 2,262,658$ 2,257,807$ 1,776,327A 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.
271126227 - 5922$ 75,733$ 70,882$ (410,598)
281127973 - 9827$ 1,530,004$ 1,525,153$ 1,043,673In the "2019" (and "2020", "2021") column:
291126075 - 9451$ 2,066,705$ 2,061,854$ 1,580,374I would use a nested XLOOKUP to find the dollar amount for that property and the GL code.
301125186 - 3373$ 1,474,791$ 1,469,940$ 988,460
311133550 - 8125$ 3,127,298$ 3,122,447$ 2,640,967
321132600 - 7438$ 677,183$ 672,332$ 190,852
331137975 - 6222$ 3,112,710$ 3,107,859$ 2,626,379
341139576 - 8574$ 2,473,931$ 2,469,080$ 1,987,600
351139134 - 9138$ 1,256,887$ 1,252,036$ 770,556
361132743 - 8957$ 2,680,288$ 2,675,437$ 2,193,957
371134019 - 566$ 329,871$ 325,020$ (156,460)
381134078 - 9237$ 2,240,165$ 2,235,314$ 1,753,834
391136227 - 5922$ 1,156,202$ 1,151,351$ 669,871
401137973 - 9827$ 793,362$ 788,511$ 307,031
411136075 - 9451$ 73,356$ 68,505$ (412,975)
421135186 - 3373$ 2,052,948$ 2,048,097$ 1,566,617
43And so on….
Output
Cell Formulas
RangeFormula
D7:D42D7=XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))
E7:E42E7=536-5387+XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))
F7:F42F7=53656-539987+XLOOKUP($B7,Properties,XLOOKUP($C7,GLCodes,'Input 2019'!$C$5:$GT$123))
Named Ranges
NameRefers ToCells
GLCodes='Input 2019'!$B$5:$B$123D7:F42
Properties='Input 2019'!$C$4:$GT$4D7:F42
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
How about for the 1st two columns
Excel Formula:
=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)))
 
Upvote 0
How about for the 1st two columns
Excel Formula:
=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.
 
Upvote 0
Are the codes in T8:T19 being input manually?
 
Upvote 0
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?
 
Upvote 0
How about
Excel Formula:
=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)))
 
Upvote 0
Solution
How about
Excel Formula:
=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!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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