Summing Duplicates in column B

[Cander]

Is there a macro that will look for duplicate rows across columns C thru G and if a duplicate or duplicates are found to sum the numbers in column H of their row in column B of their row?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
This is an example:<o></o>
The colors are here only to represent duplicates and are not normally on the sheet.<o></o>
Before macro is executed:<o></o>
<o>

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ThelmaCode	ThelmaSumDuplicates	ThelmaEaLft	ThelmaPieceSize	ThelmaMaterialUsed	ThelmaStudBearing	ThelmaFloor	ThelmaSumLFT	ThelmaCornerChannel
2	0		Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	33	7
3	0		Ea	2X4	9 STUD GR DF KD C/L	2x4 NON-BEARING STUDS	1st Floor	22	6
4	0		Ea	2X4	22 #2 DF S4S	2x4 NON-BEARING STUDS	1st Floor	12	2
5	0		Ea	2X4	22 #2 DF S4S	2x4 NON-BEARING STUDS	1st Floor	11	2
6	0		Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	33	14
7	0		Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 BEARING STUDS	1st Floor	21	12
8	0		Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	2nd Floor	44	23
9	0		Ea	2X4	9 STUD GR DF KD C/L	2x4 NON-BEARING STUDS	1st Floor	12	2
10	0		Ea	2X4	9 STUD GR DF 104 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	11	2
11	0		Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	21	4
12	0		Ea	2X4	8 STUD GR DF 92 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	56	33
13	0		Lft	2X6	10 STUD GR DF 116 1/4 KD	2x6 NON-BEARING STUDS	1st Floor	21	4
14	0		Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	1st Floor	41	21
15	0		Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	1st Floor	12	1
16	0		Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	2nd Floor	14	2
Thelma

</o>
<o></o>
After macro is executed:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	ThelmaCode	ThelmaSumDuplicates	ThelmaEaLft	ThelmaPieceSize	ThelmaMaterialUsed	ThelmaStudBearing	ThelmaFloor	ThelmaSumLFT	ThelmaCornerChannel
2	0	87	Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	33	7
3	0	121	Ea	2X4	9 STUD GR DF KD C/L	2x4 NON-BEARING STUDS	1st Floor	22	6
4	0	23	Ea	2X4	22 #2 DF S4S	2x4 NON-BEARING STUDS	1st Floor	12	2
5	0	23	Ea	2X4	22 #2 DF S4S	2x4 NON-BEARING STUDS	1st Floor	11	2
6	0	87	Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	33	14
7	0	21	Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 BEARING STUDS	1st Floor	21	12
8	0	44	Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	2nd Floor	44	23
9	0	34	Ea	2X4	9 STUD GR DF KD C/L	2x4 NON-BEARING STUDS	1st Floor	12	2
10	0	11	Ea	2X4	9 STUD GR DF 104 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	11	2
11	0	87	Ea	2X4	10 STUD GR DF 116 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	21	4
12	0	56	Ea	2X4	8 STUD GR DF 92 1/4 KD	2x4 NON-BEARING STUDS	1st Floor	56	33
13	0	21	Lft	2X6	10 STUD GR DF 116 1/4 KD	2x6 NON-BEARING STUDS	1st Floor	21	4
14	0	53	Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	1st Floor	41	21
15	0	53	Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	1st Floor	12	1
16	0	14	Ea	2X6	10 STUD GR DF 116 1/4 KD	3x6 BEARING STUDS	2nd Floor	14	2
Thelma

Thank you for your help
Charlie

 

[Mexcel]

Charlie,
You don't need a Macro for this. Simply combine the cells into a single string in a free column J. F.e. in J2:

 =C2&D2&E2&F2&G2

then in cell B2 you put

  =sumif(J$2:J$16,J2,H$2:H$16)

Then you copy this formula all the way down. Note the $ signs.
I hope I have the order of the sumif parameters correct, If not check the help.

 

[Cander]

Thank you Mexcel very nice formula but I need VBA

 

[NateO]

Why?

 

[Cander]

Hi NateO<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The Sheet is named Thelma and is used over and over. Right now you see 16 entries sometimes there can be 1000 entries. The information on the sheet is input with a userform by different people. Also right now you see columns A thru I, the columns go to AE and are growing. The macro I need is only a part of a larger macro I am fairly new and I can’t figure this part out. And the sheet is cleared after each use so Mexel’s formula would have to be input each time a new estimate was started which gives a chance for error. I see I made an error on my posted sheet…that I found by using Mexcel’s beautiful formula….B3 should read 34 not 121 Thanks again Mexcel. And why not???<o></o>
Thanks for taking interest NateO any input would be greatly appreciated.<o></o>
Charlie

 

[Mexcel]

Charlie,
You could:
1. enter the formula on a different sheet or workbook that does not get deleted;
2. re-enter te formula with the help of VBA when needed, then read the result back to VBA.
3. Use the same formula in VBA. Using Application.WorksheetFunction.
4. program the function yourself in your VBA. Using the combining of strings like in my formula and sifting through the entire list to test for duplicates for every item in the list. You need a loop to go through all rows and for each row go through another nested loop to compare to all others in the sheet. That will be very slow compared to Excel.

 

[Cander]

Bump