Need help with vlookup & MACRO

[chandra sekaran]

HI ALL,
I Have sheet 1 data same product various group sold we need
product wise sales lookup any one help me
If we add one more group it will come automattically please

I Have Sheet 1 data has like this

Product GROUP QTY AMOUNT
ABC OOOO 500 22500
ABC1 OOOO 100 4500
ABC2 OOOO 10 450
ABC AAAA 100 4500
ABC1 AAAA 200 9000
ABC2 AAAA 100 4500
ABC ZZZZZ 200 9000
ABC1 ZZZZZ 200 9000
ABC2 ZZZZZ 200 9000


I need out put like this SHEET 2

GROUP | OOOO | AAAA | ZZZZZ | TOTAL |

Product QTY AMOUNT QTY AMOUNT QTY AMOUNT QTY AMOUNT

ABC 500 22500 100 4500 200 9000 800 36000
ABC1 100 4500 200 9000 200 9000 500 22500
ABC2 10 450 100 4500 200 9000 310 13950

please reply my emailid (e-mail address removed)
Best regards
chandru

 

[isabelle]

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,