Hi all,
I've been struggling to find anything online to help with this and I need to try and find an efficient method for work, as so far I've been doing it very manually and every time new data is added I have to re-do the entire thing!
I want to create a data table using multiple inputs which repeat for every combination of the other inputs.
I have date (60 months from Jan-23 to Dec-27), account codes (151 in total), and building codes (35 in total). The table should therefore end up having 317,100 rows with each combination of date, account and building.
It should look something like this:
Building Account Date
B1 1 Jan-23
B1 1 Feb-23
B1 1 Mar-23
.... ... ...
B1 2 Jan-23
B1 2 Feb-23
B1 2 Mar-23
... ... ...
B2 1 Jan-23
B2 1 Feb-23
B2 1 Mar-23
... ... ...
B35 151 Oct-27
B35 151 Nov-27
B35 151 Dec-27
Hopefully that's clear enough to show the repeating combinations I'm trying to describe, but can always upload a pic if required!
Does anyone know a quick way to create that type of table using 3 input columns (1 for building, 1 for account codes, 1 for dates) which will auto-populate without having to manually copy and paste over and over? The best I've found so far is using a formula to repeat one input x times, then the next y times and copy and paste until if totals x, then the 3rd input z times and copy and paste until it totals x. But then have to copy and paste 35 times for each building which is really long winded!
Any help would be greatly appreciated!
I've been struggling to find anything online to help with this and I need to try and find an efficient method for work, as so far I've been doing it very manually and every time new data is added I have to re-do the entire thing!
I want to create a data table using multiple inputs which repeat for every combination of the other inputs.
I have date (60 months from Jan-23 to Dec-27), account codes (151 in total), and building codes (35 in total). The table should therefore end up having 317,100 rows with each combination of date, account and building.
It should look something like this:
Building Account Date
B1 1 Jan-23
B1 1 Feb-23
B1 1 Mar-23
.... ... ...
B1 2 Jan-23
B1 2 Feb-23
B1 2 Mar-23
... ... ...
B2 1 Jan-23
B2 1 Feb-23
B2 1 Mar-23
... ... ...
B35 151 Oct-27
B35 151 Nov-27
B35 151 Dec-27
Hopefully that's clear enough to show the repeating combinations I'm trying to describe, but can always upload a pic if required!
Does anyone know a quick way to create that type of table using 3 input columns (1 for building, 1 for account codes, 1 for dates) which will auto-populate without having to manually copy and paste over and over? The best I've found so far is using a formula to repeat one input x times, then the next y times and copy and paste until if totals x, then the 3rd input z times and copy and paste until it totals x. But then have to copy and paste 35 times for each building which is really long winded!
Any help would be greatly appreciated!