Merging question

[volvorwd]

Hello, I am new to this forum. Hope someone could help me with a merging problem in excel.

Case:

I have some huge workbooks with information like this:

I would like the information in the cells into 1 row for each "group", so i want excel to merge the cells downwards until a new cell with containment appears, except the column most to the right. Like this:

Do somebody have any idea about which function i should use to do this? Macro? Usually i could do this manually, but I have about 60 workbooks to this in, so I need a automatic way to do it.

Thanks a lot for answers!!

 

[ace19852]

Ok assuming line 1 is row 1 with data across that row and then there are 12 rows with only data in the last column. I suggest a macro to copy the data in row 2 up into row 1 and then delete row 2 and keep doing it that (in your example 12 times) until all the blank lines are removed and then repeat the process until you have compressed the file to only have lines with data in them. and forget about merging. If that what you need no problem.

 

[SalientAnimal]

I would suggest looking for a macro to remove empty rows. about 3 years ago I did something similar with a macro, but unfortunately I do not have the macro I used, nor can I remember where I got the solution. The macro basically looks at a reference cell and if it is empty it deletes the entire row. An alternative is to have your macro check is your reference cell does have a value, and if true, copy the contents of the row to a new worksheet.

 

[ace19852]

Ok if you put this under Modules as Module1 via Alt F11 and start by placing the cursor at the top of the column which is filled in it will work.

By the way I have used a comma as the seperator instead of just the space in your example. If you don't like it just change ActiveCell = Bolt1 & ", " & Bolt2 to ActiveCell = Bolt1 & " " & Bolt2 i.e. remove the comma

Sub MoveDataUp()
'
' MoveDataUp Macro
'
' Keyboard Shortcut: Ctrl+p
'
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "END"
Selection.End(xlUp).Select

Do Until ActiveCell.Offset(1, 0).Range("A1") = "END"
If ActiveCell.Offset(1, -1).Range("A1") = "" Then
Dim Bolt1 As String
Dim Bolt2 As String
Bolt1 = ""
Bolt2 = ""
Bolt1 = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
Bolt2 = ActiveCell
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell = Bolt1 & ", " & Bolt2
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Range("A1").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.EntireRow.Delete
End Sub

 

[Rick Rothstein]

Hello, I am new to this forum. Hope someone could help me with a merging problem in excel.

Case: I have some huge workbooks with information like this:

I would like the information in the cells into 1 row for each "group", so i want excel to merge the cells downwards until a new cell with containment appears, except the column most to the right. Like this:

A couple of questions for clarification...

1) Is there data in the columns either before or after what you showed that must be preserved in their current locations?

2) What columns did you show us in your examples?