Combining Multiple Rows of Data in Excel

C

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"
 
J

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
 
J

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
 
C

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
 
P

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top