Grow secondary table when additional data added to primary data

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a spreadsheet where I have imported a month electricity usage report for each month (Each Day is also broken down into 48 x 30 min periods) using Power Query and then created additional reports based on Day or Week or Month including rolling 7 day Average etc. The spreadsheet now takes several refresh clicks and over a minute for each refresh to complete. I decided to look at how I could reduce this and think about how I would do this if Power Query was not available. So hear is my question. I have the main imported table with all the raw data and want to extract all unique dates into a separate table however, all I seem to get is either the table will fill to the current length or I get #Spill Error. If I don't use a table, then something like the Unique Function will work as a dynamic array, or if I define the first and last Dates using MAX or Min Functions the the Sequence will also work as a Dynamic array. I would prefer to use tables as I like the way that formulas flow down. I would be grateful for any suggestions.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Starting off with the simplest form of extracting all dates, if you're only trying to pull the dates from the table with UNIQUE, then using other formulas to grab the relevant data then it should work fine with
Excel Formula:
=UNIQUE(Table1[Date])
Naturally, you would need to use the correct (source) table name and column name to match your data.

If you get a #SPILL error from that then it would suggest that there is existing data in the column where you have entered the formula that would be over-written if the formula was allowed to fill down.
 
Upvote 0
Starting off with the simplest form of extracting all dates, if you're only trying to pull the dates from the table with UNIQUE, then using other formulas to grab the relevant data then it should work fine with
Excel Formula:
=UNIQUE(Table1[Date])
Naturally, you would need to use the correct (source) table name and column name to match your data.

If you get a #SPILL error from that then it would suggest that there is existing data in the column where you have entered the formula that would be over-written if the formula was allowed to fill down.
Hi Jasonb75, many thanks for your reply, but as you can see from the minisheet I have now added unique would be great but it will not work within a Table.
Example File.xlsx
D
801:30:00
Data Import Sheet
 
Upvote 0
You cannot use Spill Ranges in a table.
So either you don't use a Table, or you don't use formulae that Spill.
 
Upvote 0
The data source can be a table but the dynamic array formulas need to be located in cells outside of the table.

If you need to refer to the results of the spill range as if they were in a table then you would need to use the spill reference, I believe that this is done by referring to the first row of the formulas with a # symbol after the cell reference although this is not something that I have had cause to use so far.
 
Upvote 0
Let me try again
Example File.xlsx
BCDEFGHI
1StartDate01-May-20
2EndDate30-Jun-20
3Using Unique Function (Dynamic Array)Using Unique Function (Table)
4
5DatePeriod FromPeriod ToPrice / kWhkWh UsedDate
601-May-2000:00:0000:30:005.460.00001-May-20#SPILL!
701-May-2000:30:0001:00:003.780.07802-May-20
801-May-2001:00:0001:30:003.360.07503-May-20
901-May-2001:30:0002:00:004.280.07204-May-20
1001-May-2002:00:0002:30:004.200.06605-May-20
1101-May-2002:30:0003:00:003.150.09406-May-20
1201-May-2003:00:0003:30:003.780.06607-May-20
1301-May-2003:30:0004:00:003.360.06308-May-20
1401-May-2004:00:0004:30:003.780.05209-May-20
1501-May-2004:30:0005:00:003.780.05910-May-20
1601-May-2005:00:0005:30:003.360.06311-May-20
1701-May-2005:30:0006:00:003.700.09312-May-20
1801-May-2006:00:0006:30:005.040.14113-May-20
1901-May-2006:30:0007:00:005.670.10814-May-20
2001-May-2007:00:0007:30:004.450.10615-May-20
2101-May-2007:30:0008:00:005.780.10116-May-20
2201-May-2008:00:0008:30:006.340.21517-May-20
2301-May-2008:30:0009:00:006.300.26618-May-20
2401-May-2009:00:0009:30:006.300.11419-May-20
2501-May-2009:30:0010:00:005.880.09820-May-20
2601-May-2010:00:0010:30:005.780.19421-May-20
2701-May-2010:30:0011:00:005.040.14122-May-20
2801-May-2011:00:0011:30:005.460.10623-May-20
2901-May-2011:30:0012:00:005.630.09724-May-20
3001-May-2012:00:0012:30:006.090.18725-May-20
3101-May-2012:30:0013:00:005.800.11226-May-20
3201-May-2013:00:0013:30:004.620.56527-May-20
3301-May-2013:30:0014:00:003.780.30728-May-20
3401-May-2014:00:0014:30:002.940.27029-May-20
3501-May-2014:30:0015:00:002.560.10830-May-20
3601-May-2015:00:0015:30:003.260.35431-May-20
3701-May-2015:30:0016:00:004.030.29101-Jun-20
3801-May-2016:00:0016:30:0015.860.26002-Jun-20
3901-May-2016:30:0017:00:0017.290.27603-Jun-20
4001-May-2017:00:0017:30:0017.880.18604-Jun-20
4101-May-2017:30:0018:00:0018.430.12505-Jun-20
4201-May-2018:00:0018:30:0018.720.72706-Jun-20
4301-May-2018:30:0019:00:0019.040.89607-Jun-20
4401-May-2019:00:0019:30:007.040.99708-Jun-20
4501-May-2019:30:0020:00:006.300.25809-Jun-20
4601-May-2020:00:0020:30:006.950.51210-Jun-20
4701-May-2020:30:0021:00:006.720.19511-Jun-20
4801-May-2021:00:0021:30:007.040.22112-Jun-20
4901-May-2021:30:0022:00:005.880.26813-Jun-20
5001-May-2022:00:0022:30:005.880.75414-Jun-20
5101-May-2022:30:0023:00:005.880.21215-Jun-20
5201-May-2023:00:0023:30:005.880.11316-Jun-20
5301-May-2023:30:0000:00:006.300.09517-Jun-20
5402-May-2000:00:0000:30:005.690.08118-Jun-20
5502-May-2000:30:0001:00:003.780.10919-Jun-20
5602-May-2001:00:0001:30:003.650.08620-Jun-20
5702-May-2001:30:0002:00:003.780.07721-Jun-20
5802-May-2002:00:0002:30:003.780.07422-Jun-20
5902-May-2002:30:0003:00:003.360.06123-Jun-20
6002-May-2003:00:0003:30:003.570.06324-Jun-20
6102-May-2003:30:0004:00:003.260.06325-Jun-20
6202-May-2004:00:0004:30:003.360.08026-Jun-20
6302-May-2004:30:0005:00:003.260.05227-Jun-20
6402-May-2005:00:0005:30:003.260.05828-Jun-20
6502-May-2005:30:0006:00:002.620.05929-Jun-20
6602-May-2006:00:0006:30:003.020.11030-Jun-20
6702-May-2006:30:0007:00:003.280.114
6802-May-2007:00:0007:30:003.360.121
6902-May-2007:30:0008:00:002.520.101
7002-May-2008:00:0008:30:003.570.223
7102-May-2008:30:0009:00:004.370.083
7202-May-2009:00:0009:30:005.040.149
7302-May-2009:30:0010:00:005.210.280
7402-May-2010:00:0010:30:005.460.117
7502-May-2010:30:0011:00:004.830.125
7602-May-2011:00:0011:30:005.880.105
7702-May-2011:30:0012:00:006.510.237
7802-May-2012:00:0012:30:006.720.082
7902-May-2012:30:0013:00:006.720.089
8002-May-2013:00:0013:30:006.550.127
8102-May-2013:30:0014:00:005.540.095
8202-May-2014:00:0014:30:005.460.106
8302-May-2014:30:0015:00:005.460.079
8402-May-2015:00:0015:30:004.940.082
8502-May-2015:30:0016:00:005.040.128
8602-May-2016:00:0016:30:0017.250.081
8702-May-2016:30:0017:00:0018.300.215
8802-May-2017:00:0017:30:0017.460.085
8902-May-2017:30:0018:00:0018.720.077
9002-May-2018:00:0018:30:0018.970.149
9102-May-2018:30:0019:00:0019.350.161
9202-May-2019:00:0019:30:007.240.145
9302-May-2019:30:0020:00:007.160.997
9402-May-2020:00:0020:30:007.240.231
9502-May-2020:30:0021:00:007.140.253
9602-May-2021:00:0021:30:007.240.534
9702-May-2021:30:0022:00:005.960.255
9802-May-2022:00:0022:30:006.510.233
9902-May-2022:30:0023:00:005.560.225
10002-May-2023:00:0023:30:007.140.718
10102-May-2023:30:0000:00:006.720.151
Data Import Sheet
Cell Formulas
RangeFormula
F1F1=MIN(DataTable[Date])
F2F2=MAX(DataTable[Date])
H6:H66,I6H6=UNIQUE(DataTable[Date])
Dynamic array formulas.
 
Upvote 0
You cannot use Spill Ranges in a table.
So either you don't use a Table, or you don't use formulae that Spill.
Hi Fluff, I agree that Spill Ranges wont work, but do you have any other suggestions?
 
Upvote 0
You could use
Excel Formula:
=INDEX(UNIQUE(DataTable[Date]),ROWS(I$6:I6))
 
Upvote 0
Solution
I agree that Spill Ranges wont work, but do you have any other suggestions?
If you want the result table to resize automatically as new data is added to the source (as suggested by the title of the thread) then you need to remove the table to allow the spill ranges to function.

Fluff may well know something that I don't, but to the best of my knowledge formulas alone will not add new rows to tables.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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