Remove duplicate rows and sum cells

[ToddG]

I am in desperate need of a fairly complex macro that can
do the following:

For each populated row (except row 1) in a worksheet, if
all cells in columns B-H are the same, delete the
duplicate rows and sum the cell values in column A. After
this has been done do the following:

For each populated row (except row 1) in a worksheet, if
all cells in columns B-D are the same, delete the
duplicate cell values and sum the cell values in column A.

This is pretty hard to explain so I made a few screen
shots of each step that I can email. I would appreciate
any input/advice/suggestions I could get on this. It's a
very important step in the project I am working on.
Thanks in advance for any help you could lend me,

Todd

 

[Tom Ogilvy]

Is the data sorted by B through H
so that duplicate rows will be adjacent?

 

[ToddG]

Tom,

Yes they are. I'm sorry, forgot to add that.

 

[Tom Ogilvy]

If I read it correctly you want

#1 B-D Unique Entry#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
#1 B-D Unique Entry#2 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC


but the sum in #1 would consist of the sum of 3 rows and the individual sum for the first row would be lost. Wouldn't you want to insert a row and get the sum like this:
Sum
#AA B-D Unique Entry#1
#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
#AB B-D Unique Entry#2
#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
Where #AA would be the grand total for the unique combination of values in B-D (#1) and #AB would be the grand total for the uique combination of values in B-D (#2)

but if you really only want

#AA B-D Unique Entry #1
#AB B-D Unique Entry #2

Then why do the intermediate step of getting unique rows B-H?

 

[ToddG]

Tom,

I have a few screenshots of what the end result should
look like if you would like to take a look. It should
clear things up. The reason for the intermediate step of
the unique rows of B-H was to possibly simplify things. My
thought process was that it may be easier to approch this
with a "two step" macro.

Thanks a lot,

Todd

-----Original Message-----
If I read it correctly you want

#1 B-D Unique Entry#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
#1 B-D Unique Entry#2 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC


but the sum in #1 would consist of the sum of 3 rows

and the individual sum for the first row would be lost.
Wouldn't you want to insert a row and get the sum like
this:

Sum
#AA B-D Unique Entry#1
#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
#AB B-D Unique Entry#2
#1 EA FA GA HA
#2 EB FB GB HB
#3 EC FC GC HC
Where #AA would be the grand total for the unique

combination of values in B-D (#1) and #AB would be the
grand total for the uique combination of values in B-D
(#2)

but if you really only want

#AA B-D Unique Entry #1
#AB B-D Unique Entry #2

Then why do the intermediate step of getting unique rows B-H?

--
Regards,
Tom Ogilvy


"ToddG" <[email protected]> wrote in

message news:[email protected]...

 

[Tom Ogilvy]

Have you looked at doing this with a pivot table?

If you have something to look at, then post the URL and I will take a look.

 

[ToddG]

I'll take a look at using a pivot table tonight. To be
honest i've never used them so i'll do some reading
tonight at home. One question does come to mind, will it
be possible to link the pivot table results to cells in
another worksheet? This information needs to be included
in a report that includes cells to other information. Just
a forethought.

Unfortunately I don't have a URL for the screenshots, but
could email them directly to you. If you're interested in
taking a look let me know and i'll get them to you.

Thank you very much for your time invested thus far,

Todd

 

[Tom Ogilvy]

I am not interested in receiving some multi megabyte files, If they are
less than 100K each, you can send them to (e-mail address removed)