hi chandru,

assuming that no two times the same product for a same group


Sub Macro1()
Dim sh1 As Object, sh2 As Object, rg As Range
Dim i As Long, y As Integer, cn As Integer, rw As Long
Dim GroupColumn As Integer, ProductColumn As Integer, ProductRow As Long

Sheets("Sheet2").Cells.ClearContents '<===========ATTENTION!

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")

rw = Application.Rows.Count
cn = Application.Columns.Count

sh2.Cells(1, 1) = "GROUP"
sh2.Cells(2, 1) = "Product"

GroupColumn = sh2.Cells(1, cn).End(xlToLeft).Column + 1
ProductRow = sh2.Cells(rw, 1).End(xlUp).Row + 1

For i = 2 To sh1.Range("B" & rw).End(xlUp).Row
If IsError(Application.Match(sh1.Cells(i, 2), sh2.Rows(1), 0)) Then
sh2.Cells(1, GroupColumn) = sh1.Cells(i, 2)
GroupColumn = GroupColumn + 2
If IsError(Application.Match(sh1.Cells(i, 1), sh2.Range("A:A"), 0)) Then
sh2.Cells(ProductRow, 1) = sh1.Cells(i, 1)
ProductColumn = sh2.Cells(ProductRow, cn).End(xlToLeft).Column + 1
sh2.Cells(ProductRow, ProductColumn) = sh1.Cells(i, 3)
sh2.Cells(ProductRow, ProductColumn + 1) = sh1.Cells(i, 4)
ProductRow = ProductRow + 1
Else
ProductRow = Application.Match(sh1.Cells(i, 1), sh2.Range("A:A"), 0)
sh2.Cells(ProductRow, 1) = sh1.Cells(i, 1)
ProductColumn = sh2.Cells(ProductRow, cn).End(xlToLeft).Column + 1
sh2.Cells(ProductRow, ProductColumn) = sh1.Cells(i, 3)
sh2.Cells(ProductRow, ProductColumn + 1) = sh1.Cells(i, 4)
End If
Else
GroupColumn = Application.Match(sh1.Cells(i, 2), sh2.Rows(1), 0)
If IsError(Application.Match(sh1.Cells(i, 1), sh2.Range("A:A"), 0)) Then
sh2.Cells(ProductRow, 1) = sh1.Cells(i, 1)
ProductColumn = sh2.Cells(ProductRow, cn).End(xlToLeft).Column + 1
sh2.Cells(ProductRow, ProductColumn) = sh1.Cells(i, 3)
sh2.Cells(ProductRow, ProductColumn + 1) = sh1.Cells(i, 4)
ProductRow = ProductRow + 1
Else
ProductRow = Application.Match(sh1.Cells(i, 1), sh2.Range("A:A"), 0)
sh2.Cells(ProductRow, 1) = sh1.Cells(i, 1)
ProductColumn = sh2.Cells(ProductRow, cn).End(xlToLeft).Column + 1
sh2.Cells(ProductRow, ProductColumn) = sh1.Cells(i, 3)
sh2.Cells(ProductRow, ProductColumn + 1) = sh1.Cells(i, 4)
End If
GroupColumn = GroupColumn + 2
End If
Next

GroupColumn = sh2.Cells(1, cn).End(xlToLeft).Column + 1
ProductRow = sh2.Cells(rw, 1).End(xlUp).Row

With sh2
For y = 3 To ProductRow
For i = 2 To GroupColumn Step 2
.Cells(2, i) = "QTY"
.Cells(2, i + 1) = "AMOUNT"
.Cells(y, GroupColumn + 1) = .Cells(y, GroupColumn + 1) + .Cells(y, i)
.Cells(y, GroupColumn + 2) = .Cells(y, GroupColumn + 2) + .Cells(y, i + 1)
Next
Next
..Cells(2, GroupColumn + 1) = "Total-QTY"
..Cells(2, GroupColumn + 2) = "Total-AMOUNT"
End With
End Sub


hope so help,
--
isabelle