Solved: Merge the Data

[hardeep]

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.

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)),"")

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))

Which is also Perfect.

For better Clarity See the Attached.

Kindly, see the Second file " I want This"

Thanks in Advance

Hardeep Kanwar

[Bob Phillips]

[vba]

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

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

End Sub
[/vba]

[hardeep]

[vba]

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

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

End Sub
[/vba]

Wow thats really Fast and Perfect.


Thanks XLD

Hope 2 C u again