Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with vlookup & MACRO
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 Best regards chandru |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with vlookup & MACRO
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in a Macro | Excel Programming | |||
Vlookup macro | Excel Programming | |||
VLOOKUP Macro? | Excel Worksheet Functions | |||
Vlookup in a macro | Excel Programming | |||
Please help.. VLookup Macro | Excel Programming |