Excel 2007 - macros [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
you can do a sumif
sorry byt I dont know how do it, if you can write please a code
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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"))

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!