Macro to Merge and Sum Rows

[MrGPeter]

Hi, I'm a relative newbie to Macros and this is my first posting

I have created a macro which extracts data from other spreadsheets into one master spreadsheet but I need help to then automatically tidy up the results

The spreadsheet contains several columns over several hundred rows. The first few columns of each row contain data e.g. Staff Number, Name etc. One Column, say Column G, contains a Key No followed by several further Columns each with a Unique Code e.g. 1400, 1410, 1430. Under one these Columns there will be a value.

Column A Column B Column G Column H Column I Column K etc etc
Number Name Key 1400 1410 1430
30000 West 30000ABC 1
30000 West 30000ABC 2
30000 West 30000 1
30000 West 30000ABC 5
30000 West 30000XYZ 6
30000 West 30000 2
30010 North 30010 1
30010 North 30010 1

What I want to happen is for the macro to merge like Keys and total the values within the respective columns so the end result looks like this

Column A Column B Column G Column H Column I Column K
Number Name Key 1400 1410 1430
30000 West 30000 1 2
30000 West 30000ABC 3 5
30000 West 30000XYZ 6
30010 North 30010 1 1


Can anyone help?

[Jim Thomlinson]

I would recommend to avoid the code and look at pivot tables... They will do
everything you want and a whole pile more...
--
HTH...

Jim Thomlinson


"MrGPeter" wrote:

>
> Hi, I'm a relative newbie to Macros and this is my first posting
>
> I have created a macro which extracts data from other spreadsheets into
> one master spreadsheet but I need help to then automatically tidy up the
> results
>
> The spreadsheet contains several columns over several hundred rows. The
> first few columns of each row contain data e.g. Staff Number, Name etc.
> One Column, say Column G, contains a Key No followed by several further
> Columns each with a Unique Code e.g. 1400, 1410, 1430. Under one these
> Columns there will be a value.
>
> Column A Column B Column G Column H Column I Column K etc
> etc
> Number Name Key 1400 1410
> 1430
> 30000 West 30000ABC 1
> 30000 West 30000ABC 2
> 30000 West 30000 1
> 30000 West 30000ABC
> 5
> 30000 West 30000XYZ 6
> 30000 West 30000 2
> 30010 North 30010 1
> 30010 North 30010 1
>
> What I want to happen is for the macro to merge like Keys and total the
> values within the respective columns so the end result looks like this
>
> Column A Column B Column G Column H Column I Column K
> Number Name Key 1400 1410
> 1430
> 30000 West 30000 1 2
> 30000 West 30000ABC 3
> 5
> 30000 West 30000XYZ 6
> 30010 North 30010 1 1
>
>
> Can anyone help?
>
>
> --
> MrGPeter
>
>
> ------------------------------------------------------------------------
> MrGPeter's Profile: http://www.excelforum.com/member.php...o&userid=27254
> View this thread: http://www.excelforum.com/showthread...hreadid=467661
>
>

[MrGPeter]

Thanks for the help Jim.

I tested a couple of Pivot Tables to get the desired result. Once there I created a macro which allows the Pivot Table to be created automatically, removes the Sub Total fuction on the fields and this leaves me with exactly what I need. Brill