Excel Macro to group data

[dlanden]

Hello everyone. I am trying to figure out a way to consolidate or group some data that I get on a weekly basis. I would like to create a Macro to make this process even quicker each week. Here is a sample of my data.

Column A Column B Column C Column D Column E Column F Column G Column H
1004 RIV 03 03/01/08 CR-R123 COREMART 111.12 0
1004 RIV 03 03/01/08 CR-R123 CENTRAL 123.56 0
1004 RIV 03 03/01/08 CR-R123 APNE 0 56.23
1004 RIV 03 03/02/08 CR-R222 DILLON 189.56
1004 RIV 03 03/02/08 CR-R222 APNE 87.65
1004 RIV 03 03/02/08 CR-R222 KWIK 653.25

What I need to do is group each row that has the same data in column "E" and get a total for column "G" and Column "H". I also need to keep the date that is assinged to column "D" with these corresponding totals. Here is what my data should look like once it is grouped together:

Column D Column E Column G Column H
03/01/08 CR-R123 234.68 0
03/01/08 CR-R123 0 56.23
03/02/08 CR-R222 842.81 0
03/02/08 CR-R222 0 87.65

Any help would be much appreciated.
Thanks,
Dave

 

[Andrew Poulsom]

I don't fully understand you grouping (repeated combinations of date and column E), but have you tried a pivot table?

 

[Fazza]

Hi,

If the data does not have headers, can they be added? As Excel has lots of functionality for working with data in a table - headers and data under.

F

 

[dlanden]

The reason I need to group this data is because I am dealing with thousands of rows of data. A pivot table maybe the answer that I am looking for. Thanks for your help.

 

[dlanden]

Fazza,
The data does not have headers. I am not sure about adding the data because a lot of it is non numeric data. Thanks,
Dave

 

[Fazza]

Hi,

You can only use a pivot table if you have headers. In which case, there are others options too, BTW - for example query table. A pivot table will be easiest.

F

 

[Fazza]

Dave,

My comments about adding headers is to add/insert a row with headers above the data. It is nothing to do with arithmetic addition!

F

 

[dlanden]

Fazza,
I have added headers and I am using a pivot table. Everything is working great except in the pivot table it summarizing my data on column "E" but it is splitting it out by each date (column "D"). Is there a way I can keep the date next to column "E" along with Column "G" without but not have it split it out by date?
Thanks,
Dave

 

[Andrew Poulsom]

Right click the date field and Group by day.

 

[Fazza]

Sorry, Dave.

The details of the data are confusing me. I'll have a look again tomorrow when I can.

The first three data rows end with two number fields, the last three end with one number field. Looks like something missing?

I think I know what to do but maybe best if you check & clarify. If I understand correctly, what I'm thinking of doing is something like below. This SQL could be in a query table or used in generating a pivot table or with VBA & ADO. I can elaborate tomorrow.

Regards, Fazza

SELECT D, E, Sum(G) AS [G], H
FROM table
WHERE H=0
GROUP BY D, E, H
UNION
SELECT D, E, G, Sum(H) AS [H]
FROM table
WHERE G=0
GROUP BY D, E, G