Replicating a formula pattern in Excel

Question

I have a spreadsheet that looks like this: 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.

score 1 · Accepted Answer · answered Aug 02 '16 at 09:36

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

With transpose:
- Delete the empty columns
- select your data and press CTRL+C
- go to home - paste - transpose
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

score 0 · Answer 2 · edited Mar 20 '17 at 10:04

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?

Replicating a formula pattern in Excel

2 Answers2