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"

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"

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"

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

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?