Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub-total using Advanced Filter?
Hi -
I have a workbook that's produced daily, including 3 sheets with customer-level order information. Each sheet is structured exactly the same way -- the info I need is in columns G:M. Column G is what you might call the 'key', as it contains the value (State-County-State- Zip) to be consolidated on. I need to create a consolidation sheet which will pull a list of unique places (column G) from each of the 3 data sheets, and then sub- total the line-items on those entries. In other words, the macro needs to pull all of the unique values in Column G (on sheet 1), put it on my Consolidation sheet (in col A) .... then, in Col B:D, sub- total based on the unique entries. Then, go to sheet 2 and repeat the whole process, appending the unique entries to the bottom of the Consolidation page... and again with Sheet 3. In the end, the Consolidation page should consist of sub- totals of entries for each unique place. Does that make sense? Any help is GREATLY appreciated! Thanks, ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub-total using Advanced Filter?
I have a few questions
1) Is there only 3 sheets in each workbook. If more than 3 workbooks I need to know how to identify the 3 sheets 2) You say you need Columns G:M, but g contains the Key. Which columns contain the number that need to be total. 3) How do the subtotals get added. if your data is in columns H:M (skip g which is the key) which is 6 columns, then you want the data place in columns B:D which is only 3 columns. How do 6 numbers get added and only get 3 results. "Ray" wrote: Hi - I have a workbook that's produced daily, including 3 sheets with customer-level order information. Each sheet is structured exactly the same way -- the info I need is in columns G:M. Column G is what you might call the 'key', as it contains the value (State-County-State- Zip) to be consolidated on. I need to create a consolidation sheet which will pull a list of unique places (column G) from each of the 3 data sheets, and then sub- total the line-items on those entries. In other words, the macro needs to pull all of the unique values in Column G (on sheet 1), put it on my Consolidation sheet (in col A) .... then, in Col B:D, sub- total based on the unique entries. Then, go to sheet 2 and repeat the whole process, appending the unique entries to the bottom of the Consolidation page... and again with Sheet 3. In the end, the Consolidation page should consist of sub- totals of entries for each unique place. Does that make sense? Any help is GREATLY appreciated! Thanks, ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub-total using Advanced Filter?
Thanks for replying Joel ...
Some good questions ... 1) No, there are more than 3 worksheets ... each one will end in 'data' (eg '159 data', '161 data'). Always 8 digits total, last 4 are 'data'. 2 & 3) basically the same issue ... I can't type. Column G contains the Key, with Columns H:I containing the data. The Consolidation sheet would then have same type of data (Column G contains unique locations, H:I sub-totaled data) in the same columns. Does that clarify better? Sorry for confusion! //ray On Aug 18, 9:56*pm, Joel wrote: I have a few questions 1) Is there only 3 sheets in each workbook. *If more than 3 workbooks I need to know how to identify the 3 sheets 2) You say you need Columns G:M, but g contains the Key. *Which columns contain the number that need to be total. 3) How do the subtotals get added. *if your data is in columns H:M (skip g which is the key) which is 6 columns, then you want the data place in columns B:D which is only 3 columns. *How do 6 numbers get added and only get 3 results. "Ray" wrote: Hi - I have a workbook that's produced daily, including 3 sheets with customer-level order information. *Each sheet is structured exactly the same way -- the info I need is in columns G:M. * Column G is what you might call the 'key', as it contains the value (State-County-State- Zip) to be consolidated on. *I need to create a consolidation sheet which will pull a list of unique places (column G) from each of the 3 data sheets, and then sub- total the line-items on those entries. *In other words, the macro needs to pull all of the unique values in Column G (on sheet 1), put it on my Consolidation sheet (in col A) .... then, in Col B:D, sub- total based on the unique entries. Then, go to sheet 2 and repeat the whole process, appending the unique entries to the bottom of the Consolidation page... and again with Sheet 3. *In the end, the Consolidation page should consist of sub- totals of entries for each unique place. Does that make sense? Any help is GREATLY appreciated! Thanks, ray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub-total using Advanced Filter?
Try this code
Sub consolidate() 'check if consolidation sheet exists Found = False For Each sht In Sheets If sht.Name = "Consolidation" Then Found = True Exit For End If Next sht If Found = False Then Sheets.Add after:=Sheets(Sheets.Count) Set ConSolSht = ActiveSheet ConSolSht.Name = "Consolidation" Else Set ConSolSht = Sheets("Consolidation") End If NewRow = 1 For Each sht In Sheets With sht If Right(UCase(sht.Name), 4) = "DATA" Then 'copy header row If NewRow = 1 Then .Rows(1).Copy Destination:=ConSolSht.Rows(1) NewRow = 2 End If RowCount = 2 Do While .Range("G" & RowCount) < "" Location = .Range("G" & RowCount) H_Data = .Range("H" & RowCount) I_Data = .Range("I" & RowCount) With ConSolSht 'search for ID Set c = .Columns("G").Find(what:=Location, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then 'add new row to consolidation sheet .sht.Rows(RowCount).Copy _ Destination:=.Rows(NewRow) NewRow = NewRow + 1 Else 'row exists add totals .Range("H" & c.Row) = H_Data .Range("I" & c.Row) = I_Data End If End With RowCount = RowCount + 1 Loop End If End With Next sht End Sub "Ray" wrote: Thanks for replying Joel ... Some good questions ... 1) No, there are more than 3 worksheets ... each one will end in 'data' (eg '159 data', '161 data'). Always 8 digits total, last 4 are 'data'. 2 & 3) basically the same issue ... I can't type. Column G contains the Key, with Columns H:I containing the data. The Consolidation sheet would then have same type of data (Column G contains unique locations, H:I sub-totaled data) in the same columns. Does that clarify better? Sorry for confusion! //ray On Aug 18, 9:56 pm, Joel wrote: I have a few questions 1) Is there only 3 sheets in each workbook. If more than 3 workbooks I need to know how to identify the 3 sheets 2) You say you need Columns G:M, but g contains the Key. Which columns contain the number that need to be total. 3) How do the subtotals get added. if your data is in columns H:M (skip g which is the key) which is 6 columns, then you want the data place in columns B:D which is only 3 columns. How do 6 numbers get added and only get 3 results. "Ray" wrote: Hi - I have a workbook that's produced daily, including 3 sheets with customer-level order information. Each sheet is structured exactly the same way -- the info I need is in columns G:M. Column G is what you might call the 'key', as it contains the value (State-County-State- Zip) to be consolidated on. I need to create a consolidation sheet which will pull a list of unique places (column G) from each of the 3 data sheets, and then sub- total the line-items on those entries. In other words, the macro needs to pull all of the unique values in Column G (on sheet 1), put it on my Consolidation sheet (in col A) .... then, in Col B:D, sub- total based on the unique entries. Then, go to sheet 2 and repeat the whole process, appending the unique entries to the bottom of the Consolidation page... and again with Sheet 3. In the end, the Consolidation page should consist of sub- totals of entries for each unique place. Does that make sense? Any help is GREATLY appreciated! Thanks, ray |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub-total using Advanced Filter?
I forgot to add the numbers . The code is just copying the numbers
Make this change from 'row exists add totals .Range("H" & c.Row) = H_Data .Range("I" & c.Row) = I_Data to 'row exists add totals .Range("H" & c.Row) = .Range("H" & c.Row) + H_Data .Range("I" & c.Row) = .Range("I" & c.Row) + I_Data "Ray" wrote: Thanks for replying Joel ... Some good questions ... 1) No, there are more than 3 worksheets ... each one will end in 'data' (eg '159 data', '161 data'). Always 8 digits total, last 4 are 'data'. 2 & 3) basically the same issue ... I can't type. Column G contains the Key, with Columns H:I containing the data. The Consolidation sheet would then have same type of data (Column G contains unique locations, H:I sub-totaled data) in the same columns. Does that clarify better? Sorry for confusion! //ray On Aug 18, 9:56 pm, Joel wrote: I have a few questions 1) Is there only 3 sheets in each workbook. If more than 3 workbooks I need to know how to identify the 3 sheets 2) You say you need Columns G:M, but g contains the Key. Which columns contain the number that need to be total. 3) How do the subtotals get added. if your data is in columns H:M (skip g which is the key) which is 6 columns, then you want the data place in columns B:D which is only 3 columns. How do 6 numbers get added and only get 3 results. "Ray" wrote: Hi - I have a workbook that's produced daily, including 3 sheets with customer-level order information. Each sheet is structured exactly the same way -- the info I need is in columns G:M. Column G is what you might call the 'key', as it contains the value (State-County-State- Zip) to be consolidated on. I need to create a consolidation sheet which will pull a list of unique places (column G) from each of the 3 data sheets, and then sub- total the line-items on those entries. In other words, the macro needs to pull all of the unique values in Column G (on sheet 1), put it on my Consolidation sheet (in col A) .... then, in Col B:D, sub- total based on the unique entries. Then, go to sheet 2 and repeat the whole process, appending the unique entries to the bottom of the Consolidation page... and again with Sheet 3. In the end, the Consolidation page should consist of sub- totals of entries for each unique place. Does that make sense? Any help is GREATLY appreciated! Thanks, ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Filter Code / Advanced Filter | Excel Programming | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |