Replicating a formula pattern in Excel

I have a spreadsheet that looks like this: .

I want to populate the second table using the values from the first table. The obvious (and laborious way) would be to do this manually e.g. B7 would have the formula =B2, B8 would be =D2 and so on.

Is there a way to get Excel to recognize the pattern i.e. one column in the second table is equal to every second value on a row in the first table? When I attempt to drag the bottom right of the cell an incorrect pattern is followed.

2 Answers

No, there is no exact way for that, you've two workarounds:

1. With transpose:

   • Delete the empty columns
   • select your data and press CTRL+C
   • go to home - paste - transpose

2. with formula

   • fill headers
   • in B7 enter =INDEX($B$2:$H$4,MATCH(B$6,$A$2:$A$4,0),MATCH($A7,$B$1:$H$1,0))
   • fill formula down and right

I ran out of time so I'm not promising it's efficient or even coded well, but this VBa works. (edit, and also didn't realise you had an accepted answer, but will keep this any way)

There is no undo option when running VBa, so back up first.

Option Explicit
Sub doTheThing()
Dim userStartRowInColA As Integer
userStartRowInColA = 2 'update this as needed, in your example I assume the rows start on row 2
Dim userColDifference As Integer
userColDifference = 2 'in your example, the top table is every 2 rows, hence the 2
Dim startRowInColA As Integer
startRowInColA = userStartRowInColA
Dim vals As String
vals = ""
Dim items As String
items = ""
Dim valsMissedTwo As Boolean
valsMissedTwo = False
Dim startCol As Integer
startCol = 65
Do While (True)
Dim col As String
col = Chr(startCol)
If Range(col & 1).Value = "" And valsMissedTwo Then Exit Do
Else valsMissedTwo = False
End If
If Range(col & 1).Value = "" And Not valsMissedTwo Then valsMissedTwo = True
End If
If Range(col & 1).Value <> "" Then vals = vals + Range(col & 1).Value + ","
End If
startCol = startCol + 1
Loop
Do While Range("A" & startRowInColA).Value <> ""
items = items + Range("A" & startRowInColA).Value + ","
startRowInColA = startRowInColA + 1
Loop
Dim table2StartCol As Integer
Dim table2StartRow As Integer
table2StartRow = startRowInColA + 1
table2StartCol = 66
Dim splitVals() As String
splitVals = Split(vals, ",")
Dim splitItems() As String
splitItems = Split(items, ",")
'add the items as cols
For startCol = 1 To UBound(splitItems) If splitItems(startCol - 1) <> "" Then Range(Chr(65 + startCol) & startRowInColA + 5).Value = splitItems(startCol - 1) End If
Next startCol
'add the vals on left as rows
For startCol = 1 To UBound(splitVals) If splitVals(startCol - 1) <> "" Then Range("A" & startCol + startRowInColA + 5).Value = splitVals(startCol - 1) End If
Next startCol
'now to populate
Dim sr As Integer
sr = startRowInColA + 6
Dim sc As Integer
sc = 66
Dim oSr As Integer
oSr = userStartRowInColA
Dim i As Integer
i = 0
Dim j As Integer
j = 0
Do While (True)
Do While Range(Chr(sc) & oSr).Value <> "" Range(Chr(sc + i) & sr).Value = Range(Chr(sc + j) & oSr).Value
i = i + 1
oSr = oSr + 1
Loop
j = j + userColDifference
i = 0
oSr = userStartRowInColA
sr = sr + 1
If Range("A" & sr).Value = "" Then Exit Do
End If
Loop
End Sub

Before

After

As you can see, you don't need to create the second table, it is also done automatically

How do I add VBA in MS Office?