Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT PLEASE! Filtering a Pivot Table further
Win2K Excel 2003 SP2 - I'll TRY to be specific but brief
I have a massive table 6000+ recs of customers' unit transfers by unit #, and I need to quickly find out which customers experience a split-fleet transfer. Meaning, Cust X has 10 units in Loc A and 8 units were transferred, 5 went to Loc B and 3 went to Loc C. Looks something like this (but with lots more columns): Unit # From Dist From Locn From Cust TO Dist To Location 196867 0254 13 643274 5142 10 251042 0011 11 631249 6784 10 251047 0011 11 631249 6784 10 251832 0051 13 637202 0465 10 251851 0051 13 637202 0465 10 Now I created a Pivot because it was the easiest (on my eyes) to quickly spot the split fleets as it merges the cells. So I just look for those cells. Problem is the Pivot too is huge and I need a report of JUST the customers with split fleets. The pivot looks something like this: From Cust From Dist From Locn TO Dist To Location Total 604657 0040 13 7043 10 2 604851 0011 14 6850 10 8 0051 11 5768 10 1 0722 11 5909 10 3 605058 0722 11 5909 10 1 605095 0426 10 0030 10 4 0446 10 3 609358 0449 15 5082 10 36 0451 10 6021 10 1 12 6021 10 25 609475 0040 14 7107 10 63 613537 0051 11 5768 10 6 0722 11 5909 10 12 14 5976 10 7 15 6028 10 7 From the above I only need a report that shows me: Cust(s) 605095 Because it went from 0426 to 2 diff dist 613537 Because it went from 0449 to 3 diff dist, BUT I don't need 0051 to show Notice I didn't pick 609358 0451 Because it went to the same district I can't "deselect" district #s because it will take them off of all cust(s) who have that district, and it may apply to the dist for a diff cust. I can't create a macro because that still will take too long to perform all the "hides" and I would still not be able to "hide" dist from the same cust # and just leave what I need. Plus, I keep generating new outputs to refine the data and if the Pivot changes I don't think the macro will then work. Is there anything I can do to perform this task quickly. VBA...anything! Sorry for the long thread and thank you to anyone who tries to help me. I'll be more than happy to share the file offline. -- Hile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT PLEASE! Filtering a Pivot Table further
How do you determine a split fleet? I'm not sure I understand what that
means, but if there is a calculation that can be done, you can add a helper column to your source table which identifies a particular row of data as belonging to a split fleet, and then filter the pivot table based on whether the row is identified as a split fleet. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "hile trotman" wrote: Win2K Excel 2003 SP2 - I'll TRY to be specific but brief I have a massive table 6000+ recs of customers' unit transfers by unit #, and I need to quickly find out which customers experience a split-fleet transfer. Meaning, Cust X has 10 units in Loc A and 8 units were transferred, 5 went to Loc B and 3 went to Loc C. Looks something like this (but with lots more columns): Unit # From Dist From Locn From Cust TO Dist To Location 196867 0254 13 643274 5142 10 251042 0011 11 631249 6784 10 251047 0011 11 631249 6784 10 251832 0051 13 637202 0465 10 251851 0051 13 637202 0465 10 Now I created a Pivot because it was the easiest (on my eyes) to quickly spot the split fleets as it merges the cells. So I just look for those cells. Problem is the Pivot too is huge and I need a report of JUST the customers with split fleets. The pivot looks something like this: From Cust From Dist From Locn TO Dist To Location Total 604657 0040 13 7043 10 2 604851 0011 14 6850 10 8 0051 11 5768 10 1 0722 11 5909 10 3 605058 0722 11 5909 10 1 605095 0426 10 0030 10 4 0446 10 3 609358 0449 15 5082 10 36 0451 10 6021 10 1 12 6021 10 25 609475 0040 14 7107 10 63 613537 0051 11 5768 10 6 0722 11 5909 10 12 14 5976 10 7 15 6028 10 7 From the above I only need a report that shows me: Cust(s) 605095 Because it went from 0426 to 2 diff dist 613537 Because it went from 0449 to 3 diff dist, BUT I don't need 0051 to show Notice I didn't pick 609358 0451 Because it went to the same district I can't "deselect" district #s because it will take them off of all cust(s) who have that district, and it may apply to the dist for a diff cust. I can't create a macro because that still will take too long to perform all the "hides" and I would still not be able to "hide" dist from the same cust # and just leave what I need. Plus, I keep generating new outputs to refine the data and if the Pivot changes I don't think the macro will then work. Is there anything I can do to perform this task quickly. VBA...anything! Sorry for the long thread and thank you to anyone who tries to help me. I'll be more than happy to share the file offline. -- Hile |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT PLEASE! Filtering a Pivot Table further
If you look at the pivot:
- the first cust had units go from Dist 0040 to 7043, so all his units in that district went from one place to another - if you look at cust 605095 he had 7 units in Dist 0426, but 4 of those units moved to Dist 0030 while the other 3 went to Dist 0446, so his fleet in Dist 0426 split up into 2 separate districts. - cust 609358 though it seems like units in 0451 went to 2 diff places, they did not. They all went to District 6021. The district number is the town, and the Locn # is just a billing code. I thought of adding a column to the table to flag the records that I need but I just can't figure out a logic to give me the records I need. Each unit transfer is on one line, and the unit # can also be repeated if it was transferred multiple times during the date range of the output we pulled. I tried sorting by cust, then from dist/loc, then to dist/loc. But I still need to write a formula that figures out for the same customer, lookup the from district and return record where the the from district repeats but to dist does not. I don't even know if that made sense, I can't even explain it much less figure out the logic. Here's how cust 613537 shows in the raw data (abridged): Unit # From Dist From Locn From Cust TO Dist To Location 447222 0051 11 613537 5768 10 447404 0051 11 613537 5768 10 260651 0722 11 613537 5909 10 276305 0722 11 613537 5909 10 290519 0722 11 613537 5909 10 298941 0722 11 613537 5909 10 290589 0722 14 613537 5976 10 299178 0722 14 613537 5976 10 434788 0722 14 613537 5976 10 290520 0722 15 613537 6028 10 299177 0722 15 613537 6028 10 My formula should not "flag" dist 0051 but it should flag all the recs in dist 0722. If you know how to do this, I welcome the suggestion, it's throwing me off. I'm usually pretty good at this, but this has me stumped. I think the size of the table is overwhelming me. It has way more columns (A:AQ) -- Hile "Dave F" wrote: How do you determine a split fleet? I'm not sure I understand what that means, but if there is a calculation that can be done, you can add a helper column to your source table which identifies a particular row of data as belonging to a split fleet, and then filter the pivot table based on whether the row is identified as a split fleet. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "hile trotman" wrote: Win2K Excel 2003 SP2 - I'll TRY to be specific but brief I have a massive table 6000+ recs of customers' unit transfers by unit #, and I need to quickly find out which customers experience a split-fleet transfer. Meaning, Cust X has 10 units in Loc A and 8 units were transferred, 5 went to Loc B and 3 went to Loc C. Looks something like this (but with lots more columns): Unit # From Dist From Locn From Cust TO Dist To Location 196867 0254 13 643274 5142 10 251042 0011 11 631249 6784 10 251047 0011 11 631249 6784 10 251832 0051 13 637202 0465 10 251851 0051 13 637202 0465 10 Now I created a Pivot because it was the easiest (on my eyes) to quickly spot the split fleets as it merges the cells. So I just look for those cells. Problem is the Pivot too is huge and I need a report of JUST the customers with split fleets. The pivot looks something like this: From Cust From Dist From Locn TO Dist To Location Total 604657 0040 13 7043 10 2 604851 0011 14 6850 10 8 0051 11 5768 10 1 0722 11 5909 10 3 605058 0722 11 5909 10 1 605095 0426 10 0030 10 4 0446 10 3 609358 0449 15 5082 10 36 0451 10 6021 10 1 12 6021 10 25 609475 0040 14 7107 10 63 613537 0051 11 5768 10 6 0722 11 5909 10 12 14 5976 10 7 15 6028 10 7 From the above I only need a report that shows me: Cust(s) 605095 Because it went from 0426 to 2 diff dist 613537 Because it went from 0449 to 3 diff dist, BUT I don't need 0051 to show Notice I didn't pick 609358 0451 Because it went to the same district I can't "deselect" district #s because it will take them off of all cust(s) who have that district, and it may apply to the dist for a diff cust. I can't create a macro because that still will take too long to perform all the "hides" and I would still not be able to "hide" dist from the same cust # and just leave what I need. Plus, I keep generating new outputs to refine the data and if the Pivot changes I don't think the macro will then work. Is there anything I can do to perform this task quickly. VBA...anything! Sorry for the long thread and thank you to anyone who tries to help me. I'll be more than happy to share the file offline. -- Hile |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT PLEASE! Filtering a Pivot Table further
If you look at the pivot:
- the first cust had units go from Dist 0040 to 7043, so all his units in that district went from one place to another - if you look at cust 605095 he had 7 units in Dist 0426, but 4 of those units moved to Dist 0030 while the other 3 went to Dist 0446, so his fleet in Dist 0426 split up into 2 separate districts. - cust 609358 though it seems like units in 0451 went to 2 diff places, they did not. They all went to District 6021. The district number is the town, and the Locn # is just a billing code. I thought of adding a column to the table to flag the records that I need but I just can't figure out a logic to give me the records I need. Each unit transfer is on one line, and the unit # can also be repeated if it was transferred multiple times during the date range of the output we pulled. I tried sorting by cust, then from dist/loc, then to dist/loc. But I still need to write a formula that figures out for the same customer, lookup the from district and return record where the the from district repeats but to dist does not. I don't even know if that made sense, I can't even explain it much less figure out the logic. Here's how cust 613537 shows in the raw data (abridged): Unit # From Dist From Locn From Cust TO Dist To Location 447222 0051 11 613537 5768 10 447404 0051 11 613537 5768 10 260651 0722 11 613537 5909 10 276305 0722 11 613537 5909 10 290519 0722 11 613537 5909 10 298941 0722 11 613537 5909 10 290589 0722 14 613537 5976 10 299178 0722 14 613537 5976 10 434788 0722 14 613537 5976 10 290520 0722 15 613537 6028 10 299177 0722 15 613537 6028 10 My formula should not "flag" dist 0051 but it should flag all the recs in dist 0722. If you know how to do this, I welcome the suggestion, it's throwing me off. I'm usually pretty good at this, but this has me stumped. I think the size of the table is overwhelming me. It has way more columns (A:AQ) -- Hile "Dave F" wrote: How do you determine a split fleet? I'm not sure I understand what that means, but if there is a calculation that can be done, you can add a helper column to your source table which identifies a particular row of data as belonging to a split fleet, and then filter the pivot table based on whether the row is identified as a split fleet. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "hile trotman" wrote: Win2K Excel 2003 SP2 - I'll TRY to be specific but brief I have a massive table 6000+ recs of customers' unit transfers by unit #, and I need to quickly find out which customers experience a split-fleet transfer. Meaning, Cust X has 10 units in Loc A and 8 units were transferred, 5 went to Loc B and 3 went to Loc C. Looks something like this (but with lots more columns): Unit # From Dist From Locn From Cust TO Dist To Location 196867 0254 13 643274 5142 10 251042 0011 11 631249 6784 10 251047 0011 11 631249 6784 10 251832 0051 13 637202 0465 10 251851 0051 13 637202 0465 10 Now I created a Pivot because it was the easiest (on my eyes) to quickly spot the split fleets as it merges the cells. So I just look for those cells. Problem is the Pivot too is huge and I need a report of JUST the customers with split fleets. The pivot looks something like this: From Cust From Dist From Locn TO Dist To Location Total 604657 0040 13 7043 10 2 604851 0011 14 6850 10 8 0051 11 5768 10 1 0722 11 5909 10 3 605058 0722 11 5909 10 1 605095 0426 10 0030 10 4 0446 10 3 609358 0449 15 5082 10 36 0451 10 6021 10 1 12 6021 10 25 609475 0040 14 7107 10 63 613537 0051 11 5768 10 6 0722 11 5909 10 12 14 5976 10 7 15 6028 10 7 From the above I only need a report that shows me: Cust(s) 605095 Because it went from 0426 to 2 diff dist 613537 Because it went from 0449 to 3 diff dist, BUT I don't need 0051 to show Notice I didn't pick 609358 0451 Because it went to the same district I can't "deselect" district #s because it will take them off of all cust(s) who have that district, and it may apply to the dist for a diff cust. I can't create a macro because that still will take too long to perform all the "hides" and I would still not be able to "hide" dist from the same cust # and just leave what I need. Plus, I keep generating new outputs to refine the data and if the Pivot changes I don't think the macro will then work. Is there anything I can do to perform this task quickly. VBA...anything! Sorry for the long thread and thank you to anyone who tries to help me. I'll be more than happy to share the file offline. -- Hile |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
URGENT PLEASE! Filtering a Pivot Table further
If you look at the pivot:
- the first cust had units go from Dist 0040 to 7043, so all his units in that district went from one place to another - if you look at cust 605095 he had 7 units in Dist 0426, but 4 of those units moved to Dist 0030 while the other 3 went to Dist 0446, so his fleet in Dist 0426 split up into 2 separate districts. - cust 609358 though it seems like units in 0451 went to 2 diff places, they did not. They all went to District 6021. The district number is the town, and the Locn # is just a billing code. I thought of adding a column to the table to flag the records that I need but I just can't figure out a logic to give me the records I need. Each unit transfer is on one line, and the unit # can also be repeated if it was transferred multiple times during the date range of the output we pulled. I tried sorting by cust, then from dist/loc, then to dist/loc. But I still need to write a formula that figures out for the same customer, lookup the from district and return record where the the from district repeats but to dist does not. I don't even know if that made sense, I can't even explain it much less figure out the logic. Here's how cust 613537 shows in the raw data (abridged): Unit # From Dist From Locn From Cust TO Dist To Location 447222 0051 11 613537 5768 10 447404 0051 11 613537 5768 10 260651 0722 11 613537 5909 10 276305 0722 11 613537 5909 10 290519 0722 11 613537 5909 10 298941 0722 11 613537 5909 10 290589 0722 14 613537 5976 10 299178 0722 14 613537 5976 10 434788 0722 14 613537 5976 10 290520 0722 15 613537 6028 10 299177 0722 15 613537 6028 10 My formula should not "flag" dist 0051 but it should flag all the recs in dist 0722. If you know how to do this, I welcome the suggestion, it's throwing me off. I'm usually pretty good at this, but this has me stumped. I think the size of the table is overwhelming me. It has way more columns (A:AQ) -- Hile "Dave F" wrote: How do you determine a split fleet? I'm not sure I understand what that means, but if there is a calculation that can be done, you can add a helper column to your source table which identifies a particular row of data as belonging to a split fleet, and then filter the pivot table based on whether the row is identified as a split fleet. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "hile trotman" wrote: Win2K Excel 2003 SP2 - I'll TRY to be specific but brief I have a massive table 6000+ recs of customers' unit transfers by unit #, and I need to quickly find out which customers experience a split-fleet transfer. Meaning, Cust X has 10 units in Loc A and 8 units were transferred, 5 went to Loc B and 3 went to Loc C. Looks something like this (but with lots more columns): Unit # From Dist From Locn From Cust TO Dist To Location 196867 0254 13 643274 5142 10 251042 0011 11 631249 6784 10 251047 0011 11 631249 6784 10 251832 0051 13 637202 0465 10 251851 0051 13 637202 0465 10 Now I created a Pivot because it was the easiest (on my eyes) to quickly spot the split fleets as it merges the cells. So I just look for those cells. Problem is the Pivot too is huge and I need a report of JUST the customers with split fleets. The pivot looks something like this: From Cust From Dist From Locn TO Dist To Location Total 604657 0040 13 7043 10 2 604851 0011 14 6850 10 8 0051 11 5768 10 1 0722 11 5909 10 3 605058 0722 11 5909 10 1 605095 0426 10 0030 10 4 0446 10 3 609358 0449 15 5082 10 36 0451 10 6021 10 1 12 6021 10 25 609475 0040 14 7107 10 63 613537 0051 11 5768 10 6 0722 11 5909 10 12 14 5976 10 7 15 6028 10 7 From the above I only need a report that shows me: Cust(s) 605095 Because it went from 0426 to 2 diff dist 613537 Because it went from 0449 to 3 diff dist, BUT I don't need 0051 to show Notice I didn't pick 609358 0451 Because it went to the same district I can't "deselect" district #s because it will take them off of all cust(s) who have that district, and it may apply to the dist for a diff cust. I can't create a macro because that still will take too long to perform all the "hides" and I would still not be able to "hide" dist from the same cust # and just leave what I need. Plus, I keep generating new outputs to refine the data and if the Pivot changes I don't think the macro will then work. Is there anything I can do to perform this task quickly. VBA...anything! Sorry for the long thread and thank you to anyone who tries to help me. I'll be more than happy to share the file offline. -- Hile |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a row after a pivot table | Excel Discussion (Misc queries) | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |