| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove duplicate rows and sum cells
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove duplicate rows and sum cells
Is the data sorted by B through H
so that duplicate rows will be adjacent? -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove duplicate rows and sum cells
Tom,
Yes they are. I'm sorry, forgot to add that. -----Original Message----- Is the data sorted by B through H so that duplicate rows will be adjacent? -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove duplicate rows and sum cells
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" wrote in message ... Tom, Yes they are. I'm sorry, forgot to add that. -----Original Message----- Is the data sorted by B through H so that duplicate rows will be adjacent? -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove duplicate rows and sum cells
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" wrote in message ... Tom, Yes they are. I'm sorry, forgot to add that. -----Original Message----- Is the data sorted by B through H so that duplicate rows will be adjacent? -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Remove duplicate rows and sum cells
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. -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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" wrote in message ... Tom, Yes they are. I'm sorry, forgot to add that. -----Original Message----- Is the data sorted by B through H so that duplicate rows will be adjacent? -- Regards, Tom Ogilvy "ToddG" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| remove duplicate rows but keeping all columns | Excel Worksheet Functions | |||
| Compare Two Simple Workbooks & Remove Duplicate Rows | Excel Discussion (Misc queries) | |||
| How do I remove Duplicate rows? | Excel Discussion (Misc queries) | |||
| Remove duplicate rows based on 1 specific criterion | Excel Discussion (Misc queries) | |||
| Need macro to remove duplicate rows in a wksht with same order# | Excel Discussion (Misc queries) | |||
![ToddG[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
