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?