PDA

View Full Version : Solved: Merge the Data

hardeep
07-09-2009, 12:42 AM
Dear Expert

Attached in the copy of my question.I Want to Merge the Data in Column D and Column E based on column A & Column B.:banghead:

Basically, In Column A i have Company Names, Column B have there City Names, and Column C have there Total.

Now in column D i want the Sum of Column C. Now, i am using this

IF(OR(\$A2<>\$A1,\$B2<>\$B1),SUMPRODUCT(--(\$A\$2:\$A\$33=\$A2),--(\$B\$2:\$B\$33=\$B2),(\$C\$2:\$C\$33)),""):doh:

Which is Perfect.

And in Column E i want the Average of Column D, Now i am using this

D2/SUMPRODUCT(--(\$A\$2:\$A\$33=A2),--(\$B\$2:\$B\$33=B2)):doh:

Which is also Perfect.

For better Clarity See the Attached.

Kindly, see the Second file " I want This"

Hardeep Kanwar

xld
07-09-2009, 02:26 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim EndRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 3 To LastRow + 1

If (.Cells(i, "A").Value <> .Cells(i - 1, "A").Value) Or _
(.Cells(i, "B").Value <> .Cells(i - 1, "B").Value) Then

.Cells(StartRow, "D").Resize(i - StartRow).Merge
.Cells(StartRow, "D").VerticalAlignment = xlVAlignTop
.Cells(StartRow, "E").Resize(i - StartRow).Merge
.Cells(StartRow, "E").VerticalAlignment = xlVAlignCenter
StartRow = i
End If
Next i
End With

End Sub

hardeep
07-09-2009, 03:02 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim EndRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 3 To LastRow + 1

If (.Cells(i, "A").Value <> .Cells(i - 1, "A").Value) Or _
(.Cells(i, "B").Value <> .Cells(i - 1, "B").Value) Then

.Cells(StartRow, "D").Resize(i - StartRow).Merge
.Cells(StartRow, "D").VerticalAlignment = xlVAlignTop
.Cells(StartRow, "E").Resize(i - StartRow).Merge
.Cells(StartRow, "E").VerticalAlignment = xlVAlignCenter
StartRow = i
End If
Next i
End With

End Sub

Wow thats really Fast and Perfect.

Thanks XLD

Hope 2 C u again

:beerchug: