Is there an easy way in pivots to exclude unique records? I only want to see
those that have a one to many relationship with all the rows of data i'm
showing.

I prefer not to filter the data source first as it is over 30K records and I
can't seem to figure out a complex enough formula to flag the record as I
need.

--
Hile

In Excel 2007, since you can filter pivot rows by data area results,
you add the row header into a data column as a count item, then filter
everything greater than 1.

I don't know of a way to do this in Excel 2003 and earlier, without
modifying original data. You can add a column to source data to
include a count, such as:

=COUNTIF(A2,$A$2:$A$100)

And copy down. This returns the count of A2 in its column (A2:A100 in
this case, adjust references as needed) so that you can add it to the
row field, select everything except a count of 1, then drag to report
filter (page area).


On Jan 22, 7:11*pm, Hile wrote:
Is there an easy way in pivots to exclude unique records? I only want to see
those that have a one to many relationship with all the rows of data i'm
showing.

I prefer not to filter the data source first as it is over 30K records and I
can't seem to figure out a complex enough formula to flag the record as I
need.

--
Hile

Ilia the formula doesn't seem to be working it returns a value of 1 for every
line when I know there are dups, and it's not scalable, so if I wanted to use
the same concept using dollars and I wanted to see everything over let's say
$23 in a pivot, I couldn't use this method. I often need to see pivot results
filtered based on some criteria (, <, =, etc) and would love to find a way
to do this fast.

The spreadsheet is rather complicated to explain in the post; is there a
place I can send it to you with a more detailed description of what I'm
trying to do? At very top level for this particular file, I have a report
listing units, each unit has 2 line items a lifetodate # and a yeartodate #,
for the LTD it's giving me one line per unique unit #, for YTD is giving me
multiples but not for every customer. Since the file is over 4000 rows when I
did the pivot, I have to scroll down several customer's which are ok to get
to the ones that are having this info duplicated so I can troubleshoot. I
just want to see those records where the YTD count is 1.

I'm using 2003 SP2.

--
Hile


"ilia" wrote:

In Excel 2007, since you can filter pivot rows by data area results,
you add the row header into a data column as a count item, then filter
everything greater than 1.

I don't know of a way to do this in Excel 2003 and earlier, without
modifying original data. You can add a column to source data to
include a count, such as:

=COUNTIF(A2,$A$2:$A$100)

And copy down. This returns the count of A2 in its column (A2:A100 in
this case, adjust references as needed) so that you can add it to the
row field, select everything except a count of 1, then drag to report
filter (page area).


On Jan 22, 7:11 pm, Hile wrote:
Is there an easy way in pivots to exclude unique records? I only want to see
those that have a one to many relationship with all the rows of data i'm
showing.

I prefer not to filter the data source first as it is over 30K records and I
can't seem to figure out a complex enough formula to flag the record as I
need.

--
Hile

I got the formula to work. It was backwards, the range goes first then the
criteria.

=COUNTIF(range, criteria)

=COUNTIF($A$2:$A$100,A2)


--
Hile


"ilia" wrote:

In Excel 2007, since you can filter pivot rows by data area results,
you add the row header into a data column as a count item, then filter
everything greater than 1.

I don't know of a way to do this in Excel 2003 and earlier, without
modifying original data. You can add a column to source data to
include a count, such as:

=COUNTIF(A2,$A$2:$A$100)

And copy down. This returns the count of A2 in its column (A2:A100 in
this case, adjust references as needed) so that you can add it to the
row field, select everything except a count of 1, then drag to report
filter (page area).


On Jan 22, 7:11 pm, Hile wrote:
Is there an easy way in pivots to exclude unique records? I only want to see
those that have a one to many relationship with all the rows of data i'm
showing.

I prefer not to filter the data source first as it is over 30K records and I
can't seem to figure out a complex enough formula to flag the record as I
need.

--
Hile