I need help moving data from four rows into one row

agarci1096

New Member
Joined
Nov 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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:
1606969787434.png

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:
1606969359470.png

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!
 

Attachments

  • 1606969012482.png
    1606969012482.png
    19.1 KB · Views: 4

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,344
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use sum for each 4 row for each column. For example, Input this formula at Row 2 (R2 example) for Data at Column J :
Excel Formula:
=SUM(OFFSET(J$2,(ROW()-2)*4,0,4,1))

Then Drag it right and down
 
Solution

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
143
Office Version
  1. 2019
Platform
  1. Windows
Because you can never have too many solutions. Maabadi formula is really good. I am going to save it to my library.

VBA Code:
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

New Member
Joined
Nov 25, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Use sum for each 4 row for each column. For example, Input this formula at Row 2 (R2 example) for Data at Column J :
Excel Formula:
=SUM(OFFSET(J$2,(ROW()-2)*4,0,4,1))

Then Drag it right and down
This worked perfect! Thank you!!
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,344
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You 're Welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top