|
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 |
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 |
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 . |
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 . |
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 . |
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 . |
Remove duplicate rows and sum cells
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 -----Original Message----- 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 . . |
Remove duplicate rows and sum cells
|
| All times are GMT +1. The time now is 01:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com