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

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

Thanks for the help Jim.

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

--
MrGPete

-----------------------------------------------------------------------
MrGPeter's Profile: http://www.excelforum.com/member.php...fo&userid=2725
View this thread: http://www.excelforum.com/showthread.php?threadid=46766