VBA to sum values in one column based on criteria of another column

[Jim885]

I need someone to help me with this, as I can't figure out how to do it.

The below sample is an accurate example of a much larger set of data that I need help with. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

Thanks to everyone for looking at this.

	A	B	C	D	E	F	G	H
1	Item#		Description	Quantity		Month	Year
2	1001		Item 1
3
4	Date	Transaction Description
5	5/21/2013	TX		8.00		5	2013
6	5/24/2013	TX		16.00		5	2013
7	5/31/2013	TX		20.00		5	2013	44
8	6/1/2013	TX		1.00		6	2013
9	6/4/2013	TX		2.00		6	2013
10	6/5/2013	TX		8.00		6	2013
11	6/6/2013	TX		4.00		6	2013
12	6/11/2013	TX		4.00		6	2013
13	6/11/2013	TX		16.00		6	2013
14	6/14/2013	TX		2.00		6	2013
15	6/14/2013	TX		2.00		6	2013
16	6/14/2013	TX		2.00		6	2013
17	6/24/2013	TX		1.00		6	2013
18	6/24/2013	TX		4.00		6	2013
19	6/24/2013	TX		2.00		6	2013
20	6/25/2013	TX		2.00		6	2013	50
21	7/2/2013	TX		4.00		7	2013
22	7/2/2013	TX		1.00		7	2013
23	7/11/2013	TX		4.00		7	2013
24	7/11/2013	TX		6.00		7	2013
25	7/16/2013	TX		5.00		7	2013
26	7/18/2013	TX		1.00		7	2013	21
27	8/20/2013	TX		1.00		8	2013
28	8/23/2013	TX		1.00		8	2013
29	8/23/2013	TX		4.00		8	2013
30	8/26/2013	TX		2.00		8	2013
31	8/27/2013	TX		2.00		8	2013
32	8/30/2013	TX		4.00		8	2013	14

<colgroup><col style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;" width="36"> <col style="width: 48pt;" width="64"> <col style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;" width="29"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2779;" width="76"> <col style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;" width="60"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>

 

[RadioME]

Thanks for the compliments! I'll take a look at this later, but it seems trickier than the last part so I'm not so sure I'll be able to come up with anything. I'll give it a shot though hopefully I can figure it out.

 

[RadioME]

You're new to VBA? Are you kidding me? That code is great! It did exactly what I needed.
If you're new to VBA, you're going to be amazing pretty soon. But I think you're amazing already, I have been working on this code all day and got no where.
You must have some other kind of code background, because that code you provided is great.

Thanks very much!

You don't have to take on this next step I need, but while I have your attention, I thought I'd ask.

The next thing I need is to compile this data onto another worksheet where the Item numbers are placed in Column A, the descriptions go into Column B, then the totals for each month would be dropped into the respective columns to the right. The second sheet would have headers for each column. Like this;

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item #	Description	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14	Apr-14	May-14
2	10101	Item 1	44	50	21	14	45	7	19	30	31	36	42	82	16
3	10102	Item 2	1	6	10	11	9	6	5	2	3	16	14	10	4
4	10103	Item 3	2	7	1	2	0	1	0	2	0	6	5	1	0
5	10104	Item 4	0	2	1	3	0	2	0	0	0	4	4	2	0

<tbody>
</tbody>

Since I don't have all of your data I don't know for sure but I think this might help

Sub Fixed2()

Dim MyRange As Range
Dim CopyRange As Range
Dim Cumulative As Double
Dim StartRow As Integer
Dim PasteColumn As Integer, PasteRow As Integer




'// CHANGE THESE VALUES TO FIT YOUR NEEDS //
StartRow = 5   '//Row where data begins
ItemNumber = 2 '//Number of Items
'//////////////////////////////////////////


StartRow = 5
Cumulative = 0
PasteColumn = 3
PasteRow = 2
j = 2 'Counter variable


Set MyRange = Range("F:H").SpecialCells(xlCellTypeConstants)
lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


For i = StartRow + 1 To lastrow + 1
    Cumulative = Cumulative + Range("D" & i - 1).Value
    If MyRange(i, 1) <> MyRange(i - 1, 1) Then
        Set CopyRange = MyRange(i - 1, 1).Offset(, 2)
        CopyRange.Value = Cumulative
        Cumulative = 0
    End If
Next i


'Copies Description
Range("C1:C5000").SpecialCells(xlCellTypeConstants).Copy ( _
        Worksheets(2).Range("B1"))


'Copies Item Numbers
Range("C1:C5000").SpecialCells(xlCellTypeConstants).Offset(0, -2).Copy ( _
        Worksheets(2).Range("A1"))


'Copies Numbers
For j = 1 To ItemNumber
    StartCopyRow = Cells.Find("Item " & (j), SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    If j = ItemNumber Then
        EndCopyRow = lastrow
    Else
        EndCopyRow = Cells.Find("Item " & (j + 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    End If
        
    Range("H" & StartCopyRow, "H" & EndCopyRow).SpecialCells(xlCellTypeConstants).Copy
    Worksheets(2).Range("C" & j + 1).PasteSpecial Transpose:=True
Next j




End Sub

Just change the ItemNumber and StartRow values at the top. On the last macro it was StartRow + 1, I made a change so that's not needed anymore. Now, it's just wherever the data starts which in this case appears to be row 5. I also made the assumption that the second sheet in your workbook is where the data is summarized if that's not the case just change every instance of Worksheets(2) to Worksheets(x). I hope this works!

 

[Jim885]

RadioME,
This is really good stuff. Again, you are amazing.
Your entire code works, and pastes the data to the second worksheet with the sample data.

But I'm having a problem with this line;

For j = 1 To ItemNumber
    StartCopyRow = Cells.Find("Item " & (j), SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

This line of code is searching for the item description (which are Item 1, Item 2, Item 3, etc) in the sample screen shots above. The code doesn't work with the real descriptions. Could you adjust this section so that the code searches for the item number, then transposes the relevant monthly data to sheet2?

If you are willing, I would not mind sending the actual file to you since files can't be attached in this forum.

Thanks,
Jim