Creating a repeating data table based on multiple inputs

O

opattapp

New Member
Joined
Feb 7, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
How about:

VBA Code: 
Option Explicit

Sub TableTest()
'
    Dim BuildingCodeRow     As Long, AccountCodeRow     As Long, DateRow        As Long
    Dim ArrayRow            As Long
    Dim DataRow             As Long, DataStartRow       As Long
    Dim LastAccountCode     As Long, LastBuildingCode   As Long, LastDateCount  As Long
    Dim TotalRowsNeeded     As Long
    Dim MonthYearColumn     As String
    Dim StartMonthYear      As String
    Dim MonthYearArray      As Variant, OutputArray     As Variant
'
    StartMonthYear = "Jan-23"                                                   ' <--- Set this to the start month-year of the table
    LastAccountCode = 151                                                       ' <--- Set this to the # of Account codes
    LastBuildingCode = 35                                                       ' <--- Set this to the # of Building codes
    LastDateCount = 60                                                          ' <--- Set this to the # of Month-Years to keep
    DataStartRow = 2                                                            ' <--- Set this to the row # that the data starts on
    MonthYearColumn = "C"                                                       ' <--- set this to the column letter of the MonthYearColumn
'
    TotalRowsNeeded = LastBuildingCode * LastAccountCode * LastDateCount        ' Calculate the total # of rows needed for the table
'
    Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            TotalRowsNeeded + DataStartRow - 1).NumberFormat = "mmm-yy"         ' Format the MonthYearColumn
'
'-------------------------------------------------------------------------------
'
' Create/Save the 60 month array
    Range(MonthYearColumn & DataStartRow) = StartMonthYear                      ' save the initial MonthYear to first row of data in MonthYearColumn
'
    For DataRow = DataStartRow + 1 To LastDateCount + DataStartRow - 1          ' Loop through the rows of MonthYearColumn
        Range(MonthYearColumn & DataRow) = DateAdd("m", 1, _
                Range(MonthYearColumn & DataRow - 1))                           '   Increment the month in each row
    Next                                                                        ' Loop back
'
    MonthYearArray = Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            Range(MonthYearColumn & Rows.Count).End(xlUp).Row)                  ' Save the values from MonthYearColumn into MonthYearArray
'
    Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            Range(MonthYearColumn & Rows.Count).End(xlUp).Row).ClearContents    ' Erase the values from the MonthYearColumn
'
'-------------------------------------------------------------------------------
'
    ReDim OutputArray(1 To TotalRowsNeeded, 1 To 3)
'
    For BuildingCodeRow = 1 To LastBuildingCode
        For AccountCodeRow = 1 To LastAccountCode
            For DateRow = 1 To LastDateCount
                ArrayRow = ArrayRow + 1
'
                OutputArray(ArrayRow, 1) = "B" & BuildingCodeRow
                OutputArray(ArrayRow, 2) = AccountCodeRow
                OutputArray(ArrayRow, 3) = MonthYearArray(DateRow, 1)
            Next
        Next
    Next
'
    Range("A" & DataStartRow).Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray
'
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A" & DataStartRow - 1 & ":" & _
            MonthYearColumn & TotalRowsNeeded + 1), , xlYes).Name = "Table1"       ' Convert the range to a table
End Sub
 
Upvote 1
1675834359765.png

Write down your lists on column A, B, C as many as you like. This code will spill the results on column D, E, F
VBA Code: 
Sub test()
  Dim lRowBuilding As Long, lRowAccount As Long, lRowDate As Long, r As Long
  lRowBuilding = Cells(Rows.Count, 1).End(xlUp).Row
  lRowAccount = Cells(Rows.Count, 2).End(xlUp).Row
  lRowDate = Cells(Rows.Count, 3).End(xlUp).Row
  r = 2
  Application.ScreenUpdating = False
  For i = 2 To lRowBuilding
    For j = 2 To lRowAccount
      For k = 2 To lRowDate
        Cells(r, 4).Value = Cells(i, 1).Value
        Cells(r, 5).Value = Cells(j, 2).Value
        Cells(r, 6).Value = Cells(k, 3).Value
        r = r + 1
      Next
    Next
  Next
  Application.ScreenUpdating = True
End Sub
 
Upvote 1
Solution
Flashbond said:
View attachment 84866
Write down your lists on column A, B, C as many as you like. This code will spill the results on column D, E, F
VBA Code: 
Sub test()
  Dim lRowBuilding As Long, lRowAccount As Long, lRowDate As Long, r As Long
  lRowBuilding = Cells(Rows.Count, 1).End(xlUp).Row
  lRowAccount = Cells(Rows.Count, 2).End(xlUp).Row
  lRowDate = Cells(Rows.Count, 3).End(xlUp).Row
  r = 2
  Application.ScreenUpdating = False
  For i = 2 To lRowBuilding
    For j = 2 To lRowAccount
      For k = 2 To lRowDate
        Cells(r, 4).Value = Cells(i, 1).Value
        Cells(r, 5).Value = Cells(j, 2).Value
        Cells(r, 6).Value = Cells(k, 3).Value
        r = r + 1
      Next
    Next
  Next
  Application.ScreenUpdating = True
End Sub
Click to expand...
Thank you so much! This worked perfectly!
 
Upvote 0
T
johnnyL said:
How about:

VBA Code: 
Option Explicit

Sub TableTest()
'
    Dim BuildingCodeRow     As Long, AccountCodeRow     As Long, DateRow        As Long
    Dim ArrayRow            As Long
    Dim DataRow             As Long, DataStartRow       As Long
    Dim LastAccountCode     As Long, LastBuildingCode   As Long, LastDateCount  As Long
    Dim TotalRowsNeeded     As Long
    Dim MonthYearColumn     As String
    Dim StartMonthYear      As String
    Dim MonthYearArray      As Variant, OutputArray     As Variant
'
    StartMonthYear = "Jan-23"                                                   ' <--- Set this to the start month-year of the table
    LastAccountCode = 151                                                       ' <--- Set this to the # of Account codes
    LastBuildingCode = 35                                                       ' <--- Set this to the # of Building codes
    LastDateCount = 60                                                          ' <--- Set this to the # of Month-Years to keep
    DataStartRow = 2                                                            ' <--- Set this to the row # that the data starts on
    MonthYearColumn = "C"                                                       ' <--- set this to the column letter of the MonthYearColumn
'
    TotalRowsNeeded = LastBuildingCode * LastAccountCode * LastDateCount        ' Calculate the total # of rows needed for the table
'
    Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            TotalRowsNeeded + DataStartRow - 1).NumberFormat = "mmm-yy"         ' Format the MonthYearColumn
'
'-------------------------------------------------------------------------------
'
' Create/Save the 60 month array
    Range(MonthYearColumn & DataStartRow) = StartMonthYear                      ' save the initial MonthYear to first row of data in MonthYearColumn
'
    For DataRow = DataStartRow + 1 To LastDateCount + DataStartRow - 1          ' Loop through the rows of MonthYearColumn
        Range(MonthYearColumn & DataRow) = DateAdd("m", 1, _
                Range(MonthYearColumn & DataRow - 1))                           '   Increment the month in each row
    Next                                                                        ' Loop back
'
    MonthYearArray = Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            Range(MonthYearColumn & Rows.Count).End(xlUp).Row)                  ' Save the values from MonthYearColumn into MonthYearArray
'
    Range(MonthYearColumn & DataStartRow & ":" & MonthYearColumn & _
            Range(MonthYearColumn & Rows.Count).End(xlUp).Row).ClearContents    ' Erase the values from the MonthYearColumn
'
'-------------------------------------------------------------------------------
'
    ReDim OutputArray(1 To TotalRowsNeeded, 1 To 3)
'
    For BuildingCodeRow = 1 To LastBuildingCode
        For AccountCodeRow = 1 To LastAccountCode
            For DateRow = 1 To LastDateCount
                ArrayRow = ArrayRow + 1
'
                OutputArray(ArrayRow, 1) = "B" & BuildingCodeRow
                OutputArray(ArrayRow, 2) = AccountCodeRow
                OutputArray(ArrayRow, 3) = MonthYearArray(DateRow, 1)
            Next
        Next
    Next
'
    Range("A" & DataStartRow).Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray
'
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A" & DataStartRow - 1 & ":" & _
            MonthYearColumn & TotalRowsNeeded + 1), , xlYes).Name = "Table1"       ' Convert the range to a table
End Sub
Click to expand...
Thank you very much!
 
Upvote 0
Bit late to the party, but a formula approach.
Fluff.xlsm
ABC
1
2B11Jan-23
3B11Feb-23
4B11Mar-23
5B11Apr-23
6B11May-23
7B11Jun-23
8B11Jul-23
9B11Aug-23
10B11Sep-23
11B11Oct-23
12B11Nov-23
13B11Dec-23
14B12Jan-23
15B12Feb-23
16B12Mar-23
17B12Apr-23
18B12May-23
19B12Jun-23
20B12Jul-23
21B12Aug-23
22B12Sep-23
23B12Oct-23
24B12Nov-23
25B12Dec-23
26B13Jan-23
27B13Feb-23
28B13Mar-23
29B13Apr-23
30B13May-23
31B13Jun-23
32B13Jul-23
33B13Aug-23
34B13Sep-23
35B13Oct-23
36B13Nov-23
37B13Dec-23
38B14Jan-23
Data
Cell Formulas
RangeFormula
A2:A317101A2="B"&MOD(INT(SEQUENCE(317100,,0)/1812),35)+1
B2:B317101B2=MOD(INT(SEQUENCE(317100,,0)/12),151)+1
C2:C317101C2=DATE(INT(SEQUENCE(317100,,0)/63420)+2023,MOD(SEQUENCE(317100,,0),12)+1,1)
Dynamic array formulas.
 
Upvote 0
Fluff, that is awesome. I banged my head on the wall all yesterday trying to get the formulas to spill in the right order.
 
Upvote 0
I think I will just add this little algorithm that shows where Fluff's numerals come from.

MrExcel posts20.xlsx
ABCDEFGH
1buildingsaccountsdates
2B11Jan-23permutationsbuildingsaccountsdates
3B11Feb-233171003515160
4B11Mar-2318121212
5B11Apr-2363420
Sheet3
Cell Formulas
RangeFormula
A2:A317101A2="B"&MOD(INT(SEQUENCE(E3,,0)/F4),F3)+1
B2:B317101B2=MOD(INT(SEQUENCE(E3,,0)/G4),G3)+1
C2:C317101C2=DATE(INT(SEQUENCE(E3,,0)/H5)+2023,MOD(SEQUENCE(E3,,0),H4)+1,1)
E3E3=F3*G3*H3
F4F4=G4*G3
H4H4=G4
H5H5=F4*F3
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SRMPURCHASE
  • Question Question
#NUM! ERROR
Replies
2
Views
240
SRMPURCHASE
SRMPURCHASE
V
  • Solved
Week Number
Replies
2
Views
342
vmjan02
V
C
  • Question Question
Automate a data table into a Gannt Chart
Replies
1
Views
489
Cantrecallmyusername
C
J
  • Question Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
2K
felixstraube
F
B
  • Question Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
537
B-radK
B

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

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