Combining Multiple Rows of Data in Excel
 

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"

Combining Multiple Rows of Data in Excel
 

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
---------------
Jacob Skaria


"C_Carey" wrote:

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"

Combining Multiple Rows of Data in Excel
 

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



"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"C_Carey" wrote:

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"

Combining Multiple Rows of Data in Excel
 

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

Combining Multiple Rows of Data in Excel
 

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?