I need help moving data from four rows into one row

[agarci1096]

Hi there, I have been trying to figure out how to move data from four adjacent rows so that it is lined up in one row. My spreadsheet looks like this:

I need to move the data from rows 2-5, 6-9, 10-13, etc. so that all of the numbers from those groups of 4 are lined up in a single row . It should look like this:

So basically I need to remove all of the blank spaces except for the column that does not have any data. Any help on how I would go about doing this would be great!

 

[maabadi]

Use sum for each 4 row for each column. For example, Input this formula at Row 2 (R2 example) for Data at Column J :

=SUM(OFFSET(J$2,(ROW()-2)*4,0,4,1))

Then Drag it right and down

 

[Ezguy4u]

Because you can never have too many solutions. Maabadi formula is really good. I am going to save it to my library.

Sub Move1()

Dim Cycle As Long
Dim Row1 As Long
Dim Row2 As Long
Dim Col1 As Long

Row1 = 2
Row2 = 2

For Cycle = 1 To 24

For Col1 = 10 To 13

If Cells(Row1, Col1) <> "" Then
Cells(Row2, Col1 + 8) = Cells(Row1, Col1)

End If
Next Col1
Row1 = Row1 + 1

If Cycle Mod 4 = 0 Then

Row2 = Row2 + 1

End If

Next Cycle


End Sub

 

[agarci1096]

Use sum for each 4 row for each column. For example, Input this formula at Row 2 (R2 example) for Data at Column J :

=SUM(OFFSET(J$2,(ROW()-2)*4,0,4,1))

Then Drag it right and down

This worked perfect! Thank you!!

 

[maabadi]

You 're Welcome.