Formula to move data from every third row to a column?
29,855
Try something like this. It is important that the series of 3 stays the same for tyhis to work though.
Sub SingleRow()
Range("A2").Select
Do
Selection.Cut
ActiveCell.Offset(-1, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Select
ActiveCell.EntireRow.Select
ActiveCell.EntireRow.Delete
ActiveCell.Cut
ActiveCell.Offset(-1, 2).Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Select
ActiveCell.EntireRow.Select
ActiveCell.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = "" And ActiveCell.Offset(-1, 0).Value = ""
End Sub
I tested and this should do the trick.
Related videos on Youtube
Author by
pnuts
Updated on September 18, 2022Comments
-
pnuts almost 2 years
I have an excel sheet (mac, Excel 2011) that contains names (1st row), job titles (2nd row), and company/institution (3rd row) in one big column. I am trying to separate them into three different columns. I've tried all the macros i could find. Please help.
So instead of reading like this, as it does now:
Joe Joe SomeJob MyCompany
It would read
JoeJoe | SomeJob | MyCompany
-
Admin over 11 yearsIn cell B1:
=IF(MOD(ROW(),3)=1,OFFSET($A1,COLUMN()-2,0),"")
Drag to right for 3 columns, then down to the end of your data.
-
-
Admin over 11 yearsData is in rows, not concatenated in a single cell...
-
Isaac Rabinovitch over 11 yearsOne small nitpick: this is a VBA macro, not a formula. Probably the only way to do what needs to be done, but it's important to note that you have to start up the VBA Editor to enter this code.
-
Scott - Слава Україні about 7 years(1) Rather than telling the user to insert a formula containing the value 5, and then saying “change both occurrences of number ‘5’ to match the size of your group set (… 3),” why not just present the correct formula for this question? (2) You might want to find a better resource to copy from. This answer is not particularly good, as is really depends on the data being in Column
A
, starting on Row1
, and it is not obvious how to adapt it if the data are anywhere else. Better yet, learn to solve simple questions like this by yourself, without needing to copy answers. -
Scott - Слава Україні about 7 yearsSee also How do I add VBA in MS Office?