Creating a repeating data table based on multiple inputs

opattapp · Feb 7, 2023

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!

johnnyL · Feb 8, 2023

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

Legacy 143009 · Feb 8, 2023

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

opattapp · Feb 8, 2023

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

Thank you so much! This worked perfectly!

opattapp · Feb 8, 2023

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

Thank you very much!

Legacy 143009 · Feb 8, 2023

opattapp said:
Thank you so much! This worked perfectly!

Glad it did help. Thanks for the feedback

Fluff · Feb 8, 2023

Bit late to the party, but a formula approach.

Fluff.xlsm

A

B

C

1

2

B1

1

Jan-23

3

B1

1

Feb-23

4

B1

1

Mar-23

5

B1

1

Apr-23

6

B1

1

May-23

7

B1

1

Jun-23

8

B1

1

Jul-23

9

B1

1

Aug-23

10

B1

1

Sep-23

11

B1

1

Oct-23

12

B1

1

Nov-23

13

B1

1

Dec-23

14

B1

2

Jan-23

15

B1

2

Feb-23

16

B1

2

Mar-23

17

B1

2

Apr-23

18

B1

2

May-23

19

B1

2

Jun-23

20

B1

2

Jul-23

21

B1

2

Aug-23

22

B1

2

Sep-23

23

B1

2

Oct-23

24

B1

2

Nov-23

25

B1

2

Dec-23

26

B1

3

Jan-23

27

B1

3

Feb-23

28

B1

3

Mar-23

29

B1

3

Apr-23

30

B1

3

May-23

31

B1

3

Jun-23

32

B1

3

Jul-23

33

B1

3

Aug-23

34

B1

3

Sep-23

35

B1

3

Oct-23

36

B1

3

Nov-23

37

B1

3

Dec-23

38

B1

4

Jan-23

Data

Cell Formulas
Range	Formula
A2:A317101	A2	="B"&MOD(INT(SEQUENCE(317100,,0)/1812),35)+1
B2:B317101	B2	=MOD(INT(SEQUENCE(317100,,0)/12),151)+1
C2:C317101	C2	=DATE(INT(SEQUENCE(317100,,0)/63420)+2023,MOD(SEQUENCE(317100,,0),12)+1,1)
Dynamic array formulas.

DRSteele · Feb 8, 2023

Fluff, that is awesome. I banged my head on the wall all yesterday trying to get the formulas to spill in the right order.

DRSteele · Feb 8, 2023

I think I will just add this little algorithm that shows where Fluff's numerals come from.

MrExcel posts20.xlsx

A

B

C

D

E

F

G

H

1

buildings

accounts

dates

2

B1

1

Jan-23

permutations

buildings

accounts

dates

3

B1

1

Feb-23

317100

35

151

60

4

B1

1

Mar-23

1812

12

5

B1

1

Apr-23

63420

Sheet3

Cell Formulas
Range	Formula
A2:A317101	A2	="B"&MOD(INT(SEQUENCE(E3,,0)/F4),F3)+1
B2:B317101	B2	=MOD(INT(SEQUENCE(E3,,0)/G4),G3)+1
C2:C317101	C2	=DATE(INT(SEQUENCE(E3,,0)/H5)+2023,MOD(SEQUENCE(E3,,0),H4)+1,1)
E3	E3	=F3G3H3
F4	F4	=G4*G3
H4	H4	=G4
H5	H5	=F4*F3
Dynamic array formulas.

Creating a repeating data table based on multiple inputs

opattapp

New Member

Excel Facts

johnnyL

Well-known Member

Legacy 143009

Guest

opattapp

New Member

opattapp

New Member

Legacy 143009

Guest

Fluff

MrExcel MVP, Moderator

DRSteele

Well-known Member

DRSteele

Well-known Member

Similar threads

Forum statistics

Share this page

Creating a repeating data table based on multiple inputs

opattapp

New Member

Excel Facts

johnnyL

Well-known Member

Legacy 143009

Guest

opattapp

New Member

opattapp

New Member

Legacy 143009

Guest

Fluff

MrExcel MVP, Moderator

DRSteele

Well-known Member

DRSteele

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock