Combining Multiple Rows of Data in Excel

[C_Carey]

Hi,

I am currently using Office 2007 and in my spreadsheet I have 18162 Rows of
Data. (starting at A3 and going to M18162) Column A is the only constant,
which contains customer numbers which has each customer number appearing
twice. I would like to combine the 2 rows as 1. However some customers are
not repeated.

This is my example.

(This is what I have)

A B C D E F
12 1 2
13 3 4
16 5 6
12 10 15 13
13 10 21 31
16 10 22 32
17 11 12 14 23 33

(This is what I would want)

A B C D E F
12 1 2 10 15 13
13 3 4 10 21 31
16 5 6 10 22 32
17 11 12 14 32 33


I have tried writing a few macros, but I can only delete the extra row or
move the data, not both, which is what i am trying to accomplish. My skill
are very limited so any help is greatly appricated.


Thanks"

 

[Jacob Skaria]

Make a copy of your data and try the below macro..

Sub MergeRows()
Dim lngRow As Long, lngStartRow As Long, lngFindRow As Long
lngStartRow = 3
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To _
(lngStartRow + 1) Step -1
If WorksheetFunction.CountIf(Range("A" & lngStartRow & ":A" & _
lngRow - 1), Range("A" & lngRow)) > 0 Then
lngFindRow = WorksheetFunction.Match(Range("A" & lngRow), _
Range("A" & lngStartRow & ":A" & lngRow - 1), 0)
For lngCol = 2 To 13
If Cells(lngRow, lngCol) <> "" Then
Cells(lngFindRow, lngCol) = Cells(lngFindRow, lngCol) + Cells(lngRow, lngCol)
End If
Next
Rows(lngRow).Delete
End If
Next
End Sub

If this post helps click Yes

 

[Jim Berglund]

It does work.

I remarked the following lines and it worked.

'For lngCol = 2 To 13
'If Cells(lngRow, lngCol) <> "" Then
'Cells(lngFindRow, lngCol) = Cells(lngFindRow, lngCol) + Cells(lngRow,
lngCol)
'End If
'Next

Many Thanks,
Jim

 

[chg]

What about that?

Sub FillBlanksColA()

Dim rng As Range
Dim rCell As Range
Set rng = Range(Cells(1, 1), Cells(1, 2).End(xlDown))

For Each rCell In rng
If rCell.Value = "" Then
rCell.Value = rCell.Offset(-1, 0).Value
End If
Next

End Sub

 

[plannedworker]

I need something very similar but I want to combine data when the first two
columns match not just the first. Is this easy to adapt for two?