Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H
contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Two options come to mind:
1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59*pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC * 2009040 780312737 * 8 * * WAFRIJEN * * * * 6/4/2009 * * * 204984337001 *$500.00 ABC * 2009040 780312737 * 8 * * WAFRIJEN * * * * 6/4/2009 * * * 204984337001 *$248.00 ABC * 2009040 780312737 * 8 * * WAFRIJEN * * * * 6/4/2009 * * * 204984337001 *$300.00 HER * 2009041 780748728 * 8 * * 522660V1 * 6/4/2009 *571201170018 *$5,896.00 HER * 2009041 780748728 * 8 * * 522660V1 * * * * 6/4/2009 * * * 571201170018 *$4,785.00 KLE * *2009042 780546059 * 8 * *3469-1-7 * * * * 6/2/2009 * * * 521779986001 *$4,456.00 KLE * *2009042 780546059 * 8 * *3469-1-7 * * * * 6/2/2009 * * * 521779986001 *$12,489.00 Thanks in advance!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Peruanos72,
Have you tried the built-in Subtotal option in Excel? This option usually requires you to create column headers. Once the column headers are created select the data set and run Subtotal. Subtotal will ask you to specify the "At each change in", "Use funciton", and "Add subtotal to" parameters. This should be of help to you. (Also, search for SUBTOTAL in the help documentation and read the help file). Best, Matthew Herbert "Peruanos72" wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
That worked pefectly. Now what I'd like to do is remove the groups of data
where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Matthew Herbert" wrote: Peruanos72, Have you tried the built-in Subtotal option in Excel? This option usually requires you to create column headers. Once the column headers are created select the data set and run Subtotal. Subtotal will ask you to specify the "At each change in", "Use funciton", and "Add subtotal to" parameters. This should be of help to you. (Also, search for SUBTOTAL in the help documentation and read the help file). Best, Matthew Herbert "Peruanos72" wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Thanks Jen. I'm new to writing code and Matts suggestion worked well.
Below is what I've asked Mike. Now what I'd like to do is remove the groups of data where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Jen" wrote: Two options come to mind: 1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59 pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Peruanos72,
Using a PivotTable will be the least painful, and will utilize Excel's existing functionality. The PivotTable allows you to keep your original source data in tact and let you manipulate the source data through the PivotTable's functionality. If you want VBA code to do something similar, then I can create a sample, but you can reply as to whether or not you want the VBA. The steps below make a few basic assumptions: (1) the user is using Excel 2007 (it is likely that the steps listed below will be almost identical in Excel 2003; if I remember correctly, Excel 2003 has a bit more drag and drop to the actual table itself than 2007 has), (2) the data set is the one listed in the thread and is anchored in cell A2 (i.e. the first "ABC" is in cell A2), and (3) row 1 contains a heading name for each of the columns (for purposes of illustration, I've labeled A1 as "Title" and H1 as "Amt" -- the other labels are irrelevant to this example, but are necessary if plan on using the fields in the PivotTable). Perform the following steps: (1) Select the data set (i.e. A1:H8) (2) From the Ribbon, select the following: Insert | PivotTable | PivotTable (3) Leave the default as "New Worksheet" or select "Existing Worksheet" -- I selected "Existing Worksheet" and picked cell J1 (4) Select OK (5) In "Choose fields to add to report:" check "Title" (6) Drag and drop the "Amt" to the "Value" section; your table should now show three entries: (1) ABC - 1048, (2) HER - 10681, and (3) KLE - 16945 (7) Right click the actual PivotTable and select the following: Filter | Value Filters (8) The "Value Filter" dialog box should show "Sum of Amt", "equals", and "<blank". Change "equals" to "is greater than" and enter "10000" in the "<blank" box. (9) Click OK. Your table should now show two entries: (1) HER - 10681, and (2) KLE - 16945. You now have a data set that is free of values less than 10000. Feel free to add more fields to the data table. I hope this is helpful. Best, Matt "Peruanos72" wrote: Thanks Jen. I'm new to writing code and Matts suggestion worked well. Below is what I've asked Mike. Now what I'd like to do is remove the groups of data where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Jen" wrote: Two options come to mind: 1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59 pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
That's probably the way to go. I'm using Excel 2003 and i can't for the life
of me find the filter command so only the subtotals of 10,000 or greater are visible. Thoughts? and thanks again for your help. "Matthew Herbert" wrote: Peruanos72, Using a PivotTable will be the least painful, and will utilize Excel's existing functionality. The PivotTable allows you to keep your original source data in tact and let you manipulate the source data through the PivotTable's functionality. If you want VBA code to do something similar, then I can create a sample, but you can reply as to whether or not you want the VBA. The steps below make a few basic assumptions: (1) the user is using Excel 2007 (it is likely that the steps listed below will be almost identical in Excel 2003; if I remember correctly, Excel 2003 has a bit more drag and drop to the actual table itself than 2007 has), (2) the data set is the one listed in the thread and is anchored in cell A2 (i.e. the first "ABC" is in cell A2), and (3) row 1 contains a heading name for each of the columns (for purposes of illustration, I've labeled A1 as "Title" and H1 as "Amt" -- the other labels are irrelevant to this example, but are necessary if plan on using the fields in the PivotTable). Perform the following steps: (1) Select the data set (i.e. A1:H8) (2) From the Ribbon, select the following: Insert | PivotTable | PivotTable (3) Leave the default as "New Worksheet" or select "Existing Worksheet" -- I selected "Existing Worksheet" and picked cell J1 (4) Select OK (5) In "Choose fields to add to report:" check "Title" (6) Drag and drop the "Amt" to the "Value" section; your table should now show three entries: (1) ABC - 1048, (2) HER - 10681, and (3) KLE - 16945 (7) Right click the actual PivotTable and select the following: Filter | Value Filters (8) The "Value Filter" dialog box should show "Sum of Amt", "equals", and "<blank". Change "equals" to "is greater than" and enter "10000" in the "<blank" box. (9) Click OK. Your table should now show two entries: (1) HER - 10681, and (2) KLE - 16945. You now have a data set that is free of values less than 10000. Feel free to add more fields to the data table. I hope this is helpful. Best, Matt "Peruanos72" wrote: Thanks Jen. I'm new to writing code and Matts suggestion worked well. Below is what I've asked Mike. Now what I'd like to do is remove the groups of data where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Jen" wrote: Two options come to mind: 1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59 pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Peruanos72,
I thought Excel 2003 had the same functionality, but I don't see the filter criteria within the PivotTable. This filtering option must be a new addition with the 2007 release. Nonetheless, you can get a bit creative with the Advanced Filter in combination with a PivotTable. The help documention for "Filter by using advanced criteria" is very good. What you can do is take the PivotTable data, copy and paste it to another location, and apply the advanced filter to the pasted data. For example, copy and paste the values of the PivotTable to a new worksheet, anchoring the data in A1. (Make sure the data has column headers). Insert at least three rows above row 1 (i.e. the pasted data is now anchored in A4). Duplicate the headers from row 4 in row 1. In row 2, place the criteria definition, i.e. under the Amt header (or the header that represents the amount) enter 10000. Perform Data | Filter | Advanced Filter. Select the "Action" (try both radio buttons so you can see how it behaves), select the "List range" (this is the data which is anchored in A4 and includes all the rows and columns of data), and select the "Criteria range" (this includes the headers in row 1 and the criteria below, which in this case includes row 2 where 10000 was placed). When you click OK, the data should be filtered by the criteria. This should allow ou to obtain your end result. Again, search and read the help documentation on advanced filters. Best, Matt "Peruanos72" wrote: That's probably the way to go. I'm using Excel 2003 and i can't for the life of me find the filter command so only the subtotals of 10,000 or greater are visible. Thoughts? and thanks again for your help. "Matthew Herbert" wrote: Peruanos72, Using a PivotTable will be the least painful, and will utilize Excel's existing functionality. The PivotTable allows you to keep your original source data in tact and let you manipulate the source data through the PivotTable's functionality. If you want VBA code to do something similar, then I can create a sample, but you can reply as to whether or not you want the VBA. The steps below make a few basic assumptions: (1) the user is using Excel 2007 (it is likely that the steps listed below will be almost identical in Excel 2003; if I remember correctly, Excel 2003 has a bit more drag and drop to the actual table itself than 2007 has), (2) the data set is the one listed in the thread and is anchored in cell A2 (i.e. the first "ABC" is in cell A2), and (3) row 1 contains a heading name for each of the columns (for purposes of illustration, I've labeled A1 as "Title" and H1 as "Amt" -- the other labels are irrelevant to this example, but are necessary if plan on using the fields in the PivotTable). Perform the following steps: (1) Select the data set (i.e. A1:H8) (2) From the Ribbon, select the following: Insert | PivotTable | PivotTable (3) Leave the default as "New Worksheet" or select "Existing Worksheet" -- I selected "Existing Worksheet" and picked cell J1 (4) Select OK (5) In "Choose fields to add to report:" check "Title" (6) Drag and drop the "Amt" to the "Value" section; your table should now show three entries: (1) ABC - 1048, (2) HER - 10681, and (3) KLE - 16945 (7) Right click the actual PivotTable and select the following: Filter | Value Filters (8) The "Value Filter" dialog box should show "Sum of Amt", "equals", and "<blank". Change "equals" to "is greater than" and enter "10000" in the "<blank" box. (9) Click OK. Your table should now show two entries: (1) HER - 10681, and (2) KLE - 16945. You now have a data set that is free of values less than 10000. Feel free to add more fields to the data table. I hope this is helpful. Best, Matt "Peruanos72" wrote: Thanks Jen. I'm new to writing code and Matts suggestion worked well. Below is what I've asked Mike. Now what I'd like to do is remove the groups of data where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Jen" wrote: Two options come to mind: 1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59 pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group lines of data and subtotal
Thanks Matt. I'll give this a try.
"Matthew Herbert" wrote: Peruanos72, I thought Excel 2003 had the same functionality, but I don't see the filter criteria within the PivotTable. This filtering option must be a new addition with the 2007 release. Nonetheless, you can get a bit creative with the Advanced Filter in combination with a PivotTable. The help documention for "Filter by using advanced criteria" is very good. What you can do is take the PivotTable data, copy and paste it to another location, and apply the advanced filter to the pasted data. For example, copy and paste the values of the PivotTable to a new worksheet, anchoring the data in A1. (Make sure the data has column headers). Insert at least three rows above row 1 (i.e. the pasted data is now anchored in A4). Duplicate the headers from row 4 in row 1. In row 2, place the criteria definition, i.e. under the Amt header (or the header that represents the amount) enter 10000. Perform Data | Filter | Advanced Filter. Select the "Action" (try both radio buttons so you can see how it behaves), select the "List range" (this is the data which is anchored in A4 and includes all the rows and columns of data), and select the "Criteria range" (this includes the headers in row 1 and the criteria below, which in this case includes row 2 where 10000 was placed). When you click OK, the data should be filtered by the criteria. This should allow ou to obtain your end result. Again, search and read the help documentation on advanced filters. Best, Matt "Peruanos72" wrote: That's probably the way to go. I'm using Excel 2003 and i can't for the life of me find the filter command so only the subtotals of 10,000 or greater are visible. Thoughts? and thanks again for your help. "Matthew Herbert" wrote: Peruanos72, Using a PivotTable will be the least painful, and will utilize Excel's existing functionality. The PivotTable allows you to keep your original source data in tact and let you manipulate the source data through the PivotTable's functionality. If you want VBA code to do something similar, then I can create a sample, but you can reply as to whether or not you want the VBA. The steps below make a few basic assumptions: (1) the user is using Excel 2007 (it is likely that the steps listed below will be almost identical in Excel 2003; if I remember correctly, Excel 2003 has a bit more drag and drop to the actual table itself than 2007 has), (2) the data set is the one listed in the thread and is anchored in cell A2 (i.e. the first "ABC" is in cell A2), and (3) row 1 contains a heading name for each of the columns (for purposes of illustration, I've labeled A1 as "Title" and H1 as "Amt" -- the other labels are irrelevant to this example, but are necessary if plan on using the fields in the PivotTable). Perform the following steps: (1) Select the data set (i.e. A1:H8) (2) From the Ribbon, select the following: Insert | PivotTable | PivotTable (3) Leave the default as "New Worksheet" or select "Existing Worksheet" -- I selected "Existing Worksheet" and picked cell J1 (4) Select OK (5) In "Choose fields to add to report:" check "Title" (6) Drag and drop the "Amt" to the "Value" section; your table should now show three entries: (1) ABC - 1048, (2) HER - 10681, and (3) KLE - 16945 (7) Right click the actual PivotTable and select the following: Filter | Value Filters (8) The "Value Filter" dialog box should show "Sum of Amt", "equals", and "<blank". Change "equals" to "is greater than" and enter "10000" in the "<blank" box. (9) Click OK. Your table should now show two entries: (1) HER - 10681, and (2) KLE - 16945. You now have a data set that is free of values less than 10000. Feel free to add more fields to the data table. I hope this is helpful. Best, Matt "Peruanos72" wrote: Thanks Jen. I'm new to writing code and Matts suggestion worked well. Below is what I've asked Mike. Now what I'd like to do is remove the groups of data where the subtotal is less than $10,000. I'm new to writing code but I believe I can search "total" and use if then statements to determine of the subtotal is less than $10,000 but once I determine that how can I select the range of data above and delete? "Jen" wrote: Two options come to mind: 1. Use jet to read in the data and use a query like "select sum(H) as [mytotal] from [mytable] group by a, b, c, d, e, f, g" 2. Within Excel, keep an array of current values of a,b,c,d,e,f,g. If any of the values change, dump out the value in the accumulator and reset. No sure if you need code. I am just typing as I am thinking. Jenn On Aug 25, 3:59 pm, Peruanos72 wrote: I have multiple lines of data in columns A,B, C, D,E,F,G AND H where column H contains a dollar value. The data in each column, except H is the same for multiple rows and then the data in columns A,B, C, D,E,F,G changes. This continues for multiple rows. What I need to do is somehow seperate the lines of data into groups where the data in columns A,B, C, D,E,F,G are the same so I can then subtotal column H for that group of data. Example: I need to subtotal the dollar value for ABC, HER and KLE Note: The data will change from day to day so I can't simply search for a particular value. ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $500.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $248.00 ABC 2009040 780312737 8 WAFRIJEN 6/4/2009 204984337001 $300.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $5,896.00 HER 2009041 780748728 8 522660V1 6/4/2009 571201170018 $4,785.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $4,456.00 KLE 2009042 780546059 8 3469-1-7 6/2/2009 521779986001 $12,489.00 Thanks in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort macro, subtotal and add lines after subtotal | Excel Discussion (Misc queries) | |||
Subtotal won't group | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Bolding the subtotal lines automaticlly When using the Subtotal fu | New Users to Excel | |||
Group / Outline / subtotal | Excel Discussion (Misc queries) |