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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
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.
 

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
You cannot use Spill Ranges in a table.
So either you don't use a Table, or you don't use formulae that Spill.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,312
Office Version
  1. 365
Platform
  1. Windows
You could use
Excel Formula:
=INDEX(UNIQUE(DataTable[Date]),ROWS(I$6:I6))
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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
Top