Need Marco to Combine and Sub Total data

[Gbadd]

I am working with a large amount of data that needs to be combined by RA# and then sub totaled by material number.

[TABLE="width: 839"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]RA #[/TD]
[TD]Store #[/TD]
[TD]Material[/TD]
[TD]Customer Material[/TD]
[TD]Order qty[/TD]
[TD]UoM[/TD]
[TD]Return Plant[/TD]
[TD]Box Plant[/TD]
[TD]Production plant[/TD]
[/TR]
[TR]
[TD]60178173[/TD]
[TD]7247[/TD]
[TD]38125ESS[/TD]
[TD="align: right"]14746640086[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60178173[/TD]
[TD]7247[/TD]
[TD]38125ESS[/TD]
[TD="align: right"]14746640086[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60178173[/TD]
[TD]7247[/TD]
[TD]38125ESS[/TD]
[TD="align: right"]14746640086[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60178173[/TD]
[TD]7247[/TD]
[TD]05846AQU[/TD]
[TD="align: right"]14746558466[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60178173[/TD]
[TD]7247[/TD]
[TD]05846AQU[/TD]
[TD="align: right"]14746558466[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60171864[/TD]
[TD]7247[/TD]
[TD]26342BIO[/TD]
[TD="align: right"]17541621557[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60171864[/TD]
[TD]7247[/TD]
[TD]26342BIO[/TD]
[TD="align: right"]17541621557[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON[/TD]
[/TR]
[TR]
[TD]60171864[/TD]
[TD]7247[/TD]
[TD]26342BIO[/TD]
[TD="align: right"]17541621557[/TD]
[TD="align: right"]1[/TD]
[TD]EA [/TD]
[TD]V796[/TD]
[TD]D796[/TD]
[TD]PCON
[/TD]
[/TR]
</tbody>[/TABLE]

After the macro is run the result should match the table below.

[TABLE="width: 422"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Store[/TD]
[TD]Material[/TD]
[TD]Order qty[/TD]
[TD]UoM[/TD]
[TD]RA #[/TD]
[/TR]
[TR]
[TD]7247[/TD]
[TD]38125ESS[/TD]
[TD="align: right"]3[/TD]
[TD]EA[/TD]
[TD]60178173[/TD]
[/TR]
[TR]
[TD]7247[/TD]
[TD]05846AQU[/TD]
[TD="align: right"]2[/TD]
[TD]EA[/TD]
[TD]60178173[/TD]
[/TR]
[TR]
[TD]7247[/TD]
[TD]26342BIO[/TD]
[TD="align: right"]3[/TD]
[TD]EA[/TD]
[TD]60171864[/TD]
[/TR]
</tbody>[/TABLE]

 

[engineer69]

Try the SUMPRODUCT function. It's basically a nested look-up based on multiple criteria.

 

[Gbadd]

Try the SUMPRODUCT function. It's basically a nested look-up based on multiple criteria.

Thank you for your response. I do not think SUMPRODUCT would work for me. We get raw data from the warehouse of all the materials a store returned us. We have over 900 different materials that can be returned to us and we are using a data collection scanner out in the warehouse to record what is coming in. Basically what happens is the customer has a RA # and when they receive it they can send the material(s) back to us to get credit. Once it arrives a warehouse man takes the RA paperwork off the pallet and enters the RA # and Store number into the scanner and then they scan the UPC code on the product. Some of these returns may have twenty different materials with fifteen or more of each material. At the end of the day the warehousemen dump the data into into an Excel file. Basically I end up with this large Excel file that has around 5 to 10 thousand lines on it and I need to quickly have Excel tell me the total of each material grouped by the RA number and store number.