Previous Topic Next Topic

Excel 2007 - macros

Solved/Closed
Regina - Jul 14, 2011 at 04:30 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Jul 15, 2011 at 06:06 AM
Hello,

I need to write a macro for following example.
Item Qty
c 2
B 2
b 6
C 5
c 5
a 2

Basically I want to group by Column A (i.e. Item) and Column B (i.e. qty). In this case, the result on new sheet would be as below:

Item Qty
A 2
B 8
C 10

please help me

1 reply

Answer 1 / 1
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Jul 14, 2011 at 06:34 AM
you can do a sumif
0
Regina
Jul 15, 2011 at 04:20 AM
sorry byt I dont know how do it, if you can write please a code
0
Regina
Jul 15, 2011 at 04:57 AM
I hawe this code, but he dont work like must:

Sub Disk1()
Dim lRow As Long
Dim ItemRow1, ItemRow2 As String


Columns("A:B").Select
Selection.Copy

Sheets("Sheet1").Select

Range("A1").Select
ActiveSheet.Paste

Cells.Select
Selection.Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlDescending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

lRow = 2
Do While (Cells(lRow, 1) <> "")

ItemRow1 = Cells(lRow, "A")
ItemRow2 = Cells(lRow + 1, "A")


If (ItemRow1 = ItemRow2) Then
Cells(lRow, "B") = Cells(lRow, "B") + Cells(lRow + 1, "B")
Rows(lRow + 1).Delete

Else
lRow = lRow + 1


End If
Loop

End Sub
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Jul 15, 2011 at 06:06 AM
change these two lines

ItemRow1 = Cells(lRow, "A")
ItemRow2 = Cells(lRow + 1, "A")
to

ItemRow1 = UCase(Cells(lRow, "A"))
ItemRow2 = UCase(Cells(lRow + 1, "A"))
0