URGENT PLEASE! Filtering a Pivot Table further

H

hile trotman

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.
 
D

Dave F

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
 
H

hile trotman

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)
 
H

hile trotman

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)
 
H

hile trotman

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)
 
Top