I don't even know the term for what I need ????

[donkeycrusher]

My apologies group, but I'm not even sure what to call what I'm trying to do. I've exported a bunch of real estate data. There is a set array of data across columns, then a row for each quarter of each submarket, so it's difficult to filter or chart the data the way I need. I have several hundred sub markets, 88 quarters for each sub market, and over 62,000 rows of data. I've pasted a sample of how a portion of the data is arranged for three submarkets go give an idea of the challenge I'm facing. I feel like some advance form of a pivot table or advanced query is what I'm trying to do, but this data isn't organized like any querry or pivot table that i've ever created before.

As an example, I'd like to chart Market Asking Rent for City of Albany, Clifton Park and Columbia for each quarter from 2000 through 2027 to see how they compare against each other. It would be even better if I could apply filters so that I only see submarkets with at least 1000 inventory units and 2022 Q1 rents with Market Effective Rent/SF above $1.80 and a vacancy rate (not shown on this sample) that never fell below 94% between 2008 Q1 and 2011 Q3, then chart the average market sale price per unit for each submarket that meets the test.

Period

Geography Name

CBSA Code

Geography Code

Asset Value

Inventory Units

Market Asking Rent Growth

Market Asking Rent Index

Market Asking Rent/SF

Market Asking Rent/Unit

Market Cap Rate

Market Effective Rent Growth

Market Effective Rent/SF

Market Effective Rent/Unit

Market Sale Price Growth

Market Sale Price Index

Market Sale Price Per Unit

Occupancy Rate

Stabilized Vacancy

Vacancy Rate

2008 Q1

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$286,377,696.00​

1545​

0.43%​

99.70299​

$1.26​

$1,218.74​

7.61%​

0.44%​

$1.26​

$1,213.09​

-3.81%​

124.5395​

$113,148.04​

96.52%​

3.47%​

3.48%​

2008 Q2

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$269,012,832.00​

1545​

0.48%​

100.1791​

$1.27​

$1,224.56​

7.85%​

0.49%​

$1.26​

$1,219.09​

-10.90%​

116.9003​

$106,287.17​

96.63%​

3.37%​

3.37%​

2008 Q3

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$245,145,280.00​

1545​

0.22%​

100.4005​

$1.27​

$1,227.27​

8.23%​

0.24%​

$1.27​

$1,221.99​

-17.76%​

108.0843​

$96,857.09​

96.69%​

3.31%​

3.31%​

2008 Q4

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$226,005,440.00​

1545​

-0.40%​

100​

$1.27​

$1,222.37​

8.54%​

-0.39%​

$1.26​

$1,217.26​

-22.66%​

100​

$89,294.92​

96.65%​

3.35%​

3.35%​

2009 Q1

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$213,651,488.00​

1845​

-0.94%​

99.06254​

$1.26​

$1,210.91​

8.74%​

-2.23%​

$1.23​

$1,190.15​

-24.45%​

94.08664​

$84,413.87​

88.77%​

3.50%​

11.23%​

2009 Q2

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$204,650,496.00​

1845​

-0.93%​

98.1412​

$1.24​

$1,199.65​

8.87%​

-0.80%​

$1.22​

$1,180.63​

-22.24%​

90.90114​

$80,857.56​

91.22%​

3.70%​

8.78%​

2009 Q3

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$204,191,072.00​

1845​

-0.64%​

97.51335​

$1.24​

$1,191.97​

8.86%​

-0.34%​

$1.22​

$1,176.59​

-16.50%​

90.24993​

$80,676.04​

92.88%​

3.82%​

7.12%​

2009 Q4

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$209,191,984.00​

1845​

-0.59%​

96.94279​

$1.23​

$1,185.00​

8.75%​

-0.37%​

$1.22​

$1,172.21​

-7.35%​

92.64726​

$82,651.91​

94.12%​

3.75%​

5.88%​

2010 Q1

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$221,067,440.00​

1845​

-0.39%​

96.56341​

$1.22​

$1,180.36​

8.55%​

-0.16%​

$1.21​

$1,170.31​

4.07%​

97.92011​

$87,343.91​

95.08%​

3.56%​

4.92%​

2010 Q2

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$240,299,648.00​

1845​

0.53%​

97.07393​

$1.23​

$1,186.60​

8.28%​

0.76%​

$1.22​

$1,179.15​

16.13%​

105.5606​

$94,942.57​

95.88%​

4.12%​

4.12%​

2010 Q3

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$261,514,288.00​

1845​

0.97%​

98.01321​

$1.24​

$1,198.08​

8.01%​

1.15%​

$1.24​

$1,192.66​

26.63%​

114.281​

$103,324.49​

97.26%​

2.73%​

2.74%​

2010 Q4

Albany/Schenectady/Troy - City of Albany

10580​

G46109

$280,785,152.00​

1845​

0.50%​

98.50604​

$1.25​

$1,204.11​

7.79%​

0.50%​

$1.24​

$1,198.64​

32.49%​

122.752​

$110,938.42​

97.53%​

2.47%​

2.47%​

2008 Q1

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$526,396,256.00​

4434​

0.43%​

99.70308​

$0.98​

$997.89​

7.47%​

0.44%​

$0.98​

$991.46​

-3.75%​

119.1824​

$107,801.81​

94.55%​

5.44%​

5.45%​

2008 Q2

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$494,138,656.00​

4434​

0.48%​

100.1798​

$0.99​

$1,002.66​

7.71%​

0.49%​

$0.98​

$996.33​

-10.02%​

112.8362​

$101,195.71​

94.67%​

5.32%​

5.33%​

2008 Q3

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$462,892,320.00​

4434​

0.22%​

100.4011​

$0.99​

$1,004.87​

7.96%​

0.23%​

$0.98​

$998.62​

-15.98%​

105.6249​

$94,796.71​

94.74%​

5.26%​

5.26%​

2008 Q4

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$431,591,872.00​

4434​

-0.40%​

100​

$0.99​

$1,000.86​

8.20%​

-0.40%​

$0.98​

$994.64​

-19.20%​

100​

$88,386.62​

94.68%​

5.31%​

5.32%​

2009 Q1

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$420,189,440.00​

4434​

-0.94%​

99.06155​

$0.98​

$991.46​

8.23%​

-0.98%​

$0.97​

$984.88​

-19.93%​

95.43496​

$86,051.50​

94.52%​

5.47%​

5.48%​

2009 Q2

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$402,876,960.00​

4434​

-0.93%​

98.13885​

$0.97​

$982.23​

8.36%​

-0.95%​

$0.96​

$975.55​

-17.44%​

93.15476​

$82,506.03​

94.30%​

5.70%​

5.70%​

2009 Q3

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$401,614,656.00​

4434​

-0.64%​

97.51022​

$0.96​

$975.94​

8.36%​

-0.65%​

$0.96​

$969.24​

-12.45%​

92.4797​

$82,247.52​

94.16%​

5.84%​

5.84%​

2009 Q4

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$411,314,656.00​

4434​

-0.59%​

96.93894​

$0.96​

$970.22​

8.25%​

-0.58%​

$0.95​

$963.59​

-5.17%​

94.82749​

$84,234.01​

94.24%​

5.75%​

5.76%​

2010 Q1

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$433,742,720.00​

4434​

-0.39%​

96.55889​

$0.95​

$966.42​

8.07%​

-0.37%​

$0.95​

$960.02​

4.92%​

100.1312​

$88,827.10​

94.46%​

5.54%​

5.54%​

2010 Q2

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$471,340,992.00​

4434​

0.53%​

97.0697​

$0.96​

$971.53​

7.81%​

0.57%​

$0.95​

$965.46​

15.79%​

107.8623​

$96,526.93​

94.79%​

5.20%​

5.21%​

2010 Q3

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$512,953,952.00​

4434​

0.97%​

98.01003​

$0.97​

$980.94​

7.56%​

1.00%​

$0.96​

$975.12​

26.18%​

116.6943​

$105,048.93​

95.17%​

4.82%​

4.83%​

2010 Q4

Albany/Schenectady/Troy - Clifton Park

10580​

G46110

$549,853,696.00​

4434​

0.50%​

98.50333​

$0.97​

$985.88​

7.35%​

0.52%​

$0.97​

$980.22​

32.00%​

125.1723​

$112,605.71​

95.41%​

4.58%​

4.59%​

2008 Q1

Albany/Schenectady/Troy - Columbia County

G52482

$77,364,656.00​

911​

0.42%​

99.7052​

$1.01​

$1,117.76​

9.18%​

0.72%​

$1.00​

$1,106.87​

-4.83%​

120.0451​

$74,893.18​

90.83%​

9.18%​

9.17%​

2008 Q2

Albany/Schenectady/Troy - Columbia County

G52482

$73,055,912.00​

911​

0.47%​

100.1766​

$1.01​

$1,123.04​

9.43%​

0.79%​

$1.01​

$1,115.66​

-10.59%​

113.7937​

$70,722.09​

90.77%​

9.24%​

9.23%​

2008 Q3

Albany/Schenectady/Troy - Columbia County

G52482

$68,426,848.00​

911​

0.22%​

100.396​

$1.02​

$1,125.50​

9.73%​

0.52%​

$1.01​

$1,121.49​

-15.99%​

106.7621​

$66,240.90​

90.73%​

9.28%​

9.27%​

2008 Q4

Albany/Schenectady/Troy - Columbia County

G52482

$63,841,500.00​

911​

-0.39%​

100​

$1.01​

$1,121.06​

10.01%​

-1.85%​

$0.99​

$1,100.75​

-19.81%​

100​

$61,802.03​

90.70%​

9.30%​

9.30%​

2009 Q1

Albany/Schenectady/Troy - Columbia County

G52482

$60,051,148.00​

911​

-0.93%​

99.07499​

$1.00​

$1,110.69​

10.26%​

-0.82%​

$0.98​

$1,091.75​

-21.38%​

94.38293​

$58,132.77​

90.69%​

9.31%​

9.31%​

2009 Q2

Albany/Schenectady/Troy - Columbia County

G52482

$57,624,132.00​

911​

-0.91%​

98.17137​

$0.99​

$1,100.56​

10.42%​

-0.58%​

$0.98​

$1,085.39​

-19.96%​

91.07556​

$55,783.28​

90.67%​

9.34%​

9.33%​

2009 Q3

Albany/Schenectady/Troy - Columbia County

G52482

$57,480,784.00​

911​

-0.63%​

97.55567​

$0.99​

$1,093.66​

10.41%​

-0.40%​

$0.98​

$1,081.09​

-15.22%​

90.51343​

$55,644.51​

90.63%​

9.38%​

9.37%​

2009 Q4

Albany/Schenectady/Troy - Columbia County

G52482

$58,970,052.00​

911​

-0.57%​

96.99653​

$0.98​

$1,087.39​

10.27%​

-0.35%​

$0.97​

$1,077.29​

-7.12%​

92.88363​

$57,086.21​

90.65%​

9.36%​

9.35%​

2010 Q1

Albany/Schenectady/Troy - Columbia County

G52482

$62,182,496.00​

911​

-0.39%​

96.62305​

$0.98​

$1,083.20​

10.04%​

-0.19%​

$0.97​

$1,075.28​

4.07%​

98.22821​

$60,196.03​

90.82%​

9.20%​

9.18%​

2010 Q2

Albany/Schenectady/Troy - Columbia County

G52482

$67,759,448.00​

911​

0.52%​

97.12127​

$0.98​

$1,088.79​

9.70%​

0.67%​

$0.98​

$1,082.53​

16.23%​

105.8541​

$65,594.82​

91.01%​

9.00%​

8.99%​

2010 Q3

Albany/Schenectady/Troy - Columbia County

G52482

$73,636,184.00​

911​

0.94%​

98.03613​

$0.99​

$1,099.04​

9.39%​

0.93%​

$0.99​

$1,092.58​

26.52%​

114.5219​

$71,283.82​

91.25%​

8.77%​

8.75%​

2010 Q4

Albany/Schenectady/Troy - Columbia County

G52482

$78,852,376.00​

911​

0.49%​

98.52093​

$1.00​

$1,104.48​

9.13%​

0.50%​

$0.99​

$1,097.99​

32.06%​

122.6657​

$76,333.37​

91.53%​

8.49%​

8.47%​

2008 Q1

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$622,695,616.00​

5845​

0.43%​

99.67051​

$1.24​

$994.99​

8.24%​

0.44%​

$1.24​

$988.61​

-5.17%​

121.9036​

$82,421.65​

94.75%​

5.25%​

5.25%​

2008 Q2

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$583,974,784.00​

5845​

0.48%​

100.15​

$1.25​

$999.78​

8.49%​

0.49%​

$1.24​

$993.50​

-11.17%​

115.1386​

$77,296.47​

94.80%​

5.20%​

5.20%​

2008 Q3

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$542,193,472.00​

5845​

0.23%​

100.379​

$1.25​

$1,002.07​

8.82%​

0.25%​

$1.25​

$995.94​

-17.00%​

107.2471​

$71,766.18​

94.87%​

5.13%​

5.13%​

2008 Q4

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$502,829,472.00​

5845​

-0.38%​

100​

$1.25​

$998.28​

9.12%​

-0.36%​

$1.24​

$992.32​

-21.14%​

100​

$66,555.85​

94.90%​

5.10%​

5.10%​

2009 Q1

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$473,897,664.00​

5845​

-0.89%​

99.10535​

$1.24​

$989.35​

9.34%​

-0.97%​

$1.23​

$982.66​

-23.62%​

93.11037​

$62,726.36​

94.86%​

5.14%​

5.14%​

2009 Q2

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$437,545,472.00​

5851​

-0.87%​

98.23913​

$1.23​

$980.70​

9.56%​

-0.88%​

$1.22​

$974.04​

-22.93%​

88.73822​

$57,914.69​

94.77%​

5.23%​

5.23%​

2009 Q3

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$436,419,936.00​

5851​

-0.59%​

97.6574​

$1.22​

$974.90​

9.56%​

-0.59%​

$1.21​

$968.33​

-18.91%​

86.96713​

$57,765.71​

94.71%​

5.29%​

5.29%​

2009 Q4

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$446,996,288.00​

5851​

-0.54%​

97.13196​

$1.21​

$969.65​

9.44%​

-0.53%​

$1.20​

$963.15​

-10.81%​

89.19487​

$59,165.62​

94.72%​

5.28%​

5.28%​

2010 Q1

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$471,382,976.00​

5851​

-0.35%​

96.79206​

$1.21​

$966.26​

9.23%​

-0.33%​

$1.20​

$960.00​

1.03%​

94.06736​

$62,393.51​

94.85%​

5.15%​

5.15%​

2010 Q2

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$510,429,312.00​

5851​

0.53%​

97.30875​

$1.22​

$971.42​

8.95%​

0.57%​

$1.21​

$965.52​

13.84%​

101.0176​

$67,561.79​

95.10%​

4.90%​

4.90%​

2010 Q3

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$552,564,608.00​

5851​

0.96%​

98.23806​

$1.23​

$980.69​

8.67%​

0.99%​

$1.22​

$975.08​

25.28%​

108.952​

$73,138.93​

95.39%​

4.61%​

4.61%​

2010 Q4

Albany/Schenectady/Troy - Downtown Albany

10580​

G52200

$591,900,672.00​

5851​

0.51%​

98.73859​

$1.23​

$985.69​

8.44%​

0.53%​

$1.23​

$980.21​

30.75%​

116.6215​

$78,345.56​

95.60%​

4.40%​

4.40%​

2008 Q1

Albany/Schenectady/Troy - Fulton County

G67097

$41,755,216.00​

689​

0.43%​

99.70311​

$0.62​

$631.67​

8.84%​

0.42%​

$0.61​

$626.33​

-3.63%​

120.4828​

$60,602.63​

89.72%​

10.28%​

10.28%​

2008 Q2

Albany/Schenectady/Troy - Fulton County

G67097

$39,372,088.00​

689​

0.48%​

100.1804​

$0.62​

$634.69​

9.10%​

0.48%​

$0.62​

$629.32​

-10.02%​

114.0339​

$57,143.81​

89.69%​

10.31%​

10.31%​

2008 Q3

Albany/Schenectady/Troy - Fulton County

G67097

$36,797,156.00​

689​

0.22%​

100.4021​

$0.62​

$636.10​

9.40%​

0.22%​

$0.62​

$630.71​

-15.94%​

106.8539​

$53,406.61​

89.70%​

10.30%​

10.30%​

2008 Q4

Albany/Schenectady/Troy - Fulton County

G67097

$34,330,272.00​

689​

-0.40%​

100​

$0.62​

$633.55​

9.68%​

-0.40%​

$0.62​

$628.19​

-20.11%​

100​

$49,826.23​

89.72%​

10.28%​

10.28%​

2009 Q1

Albany/Schenectady/Troy - Fulton County

G67097

$32,284,302.00​

689​

-0.93%​

99.06986​

$0.61​

$627.65​

9.92%​

-0.93%​

$0.61​

$622.37​

-21.71%​

94.32062​

$46,856.75​

89.74%​

10.26%​

10.26%​

2009 Q2

Albany/Schenectady/Troy - Fulton County

G67097

$30,924,010.00​

689​

-0.92%​

98.15977​

$0.61​

$621.89​

10.09%​

-0.92%​

$0.60​

$616.62​

-20.31%​

90.86905​

$44,882.45​

89.73%​

10.26%​

10.27%​

2009 Q3

Albany/Schenectady/Troy - Fulton County

G67097

$30,760,948.00​

689​

-0.63%​

97.5378​

$0.60​

$617.95​

10.10%​

-0.63%​

$0.60​

$612.73​

-15.70%​

90.07315​

$44,645.79​

89.71%​

10.28%​

10.29%​

2009 Q4

Albany/Schenectady/Troy - Fulton County

G67097

$31,461,250.00​

689​

-0.58%​

96.97346​

$0.60​

$614.37​

9.98%​

-0.58%​

$0.60​

$609.18​

-6.89%​

93.10507​

$45,662.19​

89.68%​

10.31%​

10.32%​

2010 Q1

Albany/Schenectady/Troy - Fulton County

G67097

$34,059,372.00​

689​

-0.39%​

96.59639​

$0.60​

$611.98​

9.70%​

-0.39%​

$0.59​

$606.80​

4.48%​

98.54304​

$49,433.05​

89.66%​

10.34%​

10.34%​

2010 Q2

Albany/Schenectady/Troy - Fulton County

G67097

$36,384,444.00​

689​

0.52%​

97.09685​

$0.60​

$615.15​

9.43%​

0.52%​

$0.60​

$609.95​

17.04%​

106.3532​

$52,807.61​

89.66%​

10.34%​

10.34%​

2010 Q3

Albany/Schenectady/Troy - Fulton County

G67097

$39,537,868.00​

689​

0.95%​

98.02322​

$0.61​

$621.02​

9.13%​

0.96%​

$0.60​

$615.79​

26.96%​

114.3567​

$57,384.43​

89.68%​

10.31%​

10.32%​

2010 Q4

Albany/Schenectady/Troy - Fulton County

G67097

$42,335,964.00​

689​

0.49%​

98.50489​

$0.61​

$624.08​

8.88%​

0.49%​

$0.61​

$618.83​

31.56%​

122.4862​

$61,445.52​

89.74%​

10.25%​

10.26%​

 

[SanjayGMusafir]

Interesting, it's just that you are trying to explore the power of Excel. I would suggest you to update version of Excel you are using in your profile. Though it is not needed here right now. But soon your questions will arise and someone will need to understand which version of Excel you are working with.

As of now what you need to do is split using - Text to Columns

Period to Year and Quarter in two columns
Geography name to 3-4 columns until every sub-region is in seperate column

Once you are done with this your data is ready to be played with the way you want to.

 

[donkeycrusher]

Interesting, it's just that you are trying to explore the power of Excel. I would suggest you to update version of Excel you are using in your profile. Though it is not needed here right now. But soon your questions will arise and someone will need to understand which version of Excel you are working with.

As of now what you need to do is split using - Text to Columns

Period to Year and Quarter in two columns
Geography name to 3-4 columns until every sub-region is in seperate column

Once you are done with this your data is ready to be played with the way you want to.

Thanks Sanjay! I didn't even realize I had a version of Excel indicated. I'll update it now, then get to work with the guidance you gave. Thank you again.

 

[SanjayGMusafir]

Thanks Sanjay! I didn't even realize I had a version of Excel indicated. I'll update it now, then get to work with the guidance you gave. Thank you again.

With Excel 365 once you have restructured your data - You will realize that you have so many options available to analyze your data and filtering the way you want it to appear.

Also hope you are using structured tables - that makes using data more dynamic in many ways.

try that too.

 

[SanjayGMusafir]

Also, if I may suggest your geography column has / & - to separate sub-regions -

First split using / as delimiter then - as delimiter

Once that is done you will find some extra spaces in cells to handle. that can also handled once you reach there.