filtering data to include values only if x and y exist
 

Excel 2003
Hope somebody can help with the following. I have information x and y in the
same column concerning A (persons)in a pivot table. I would like to show
information x and y per person only if x and y both have a value. If x or y
doesnt have a value then the person in question should not be shown in the
report. I am not sure if pivot table is the best tool for this, but I do not
know of any other better tool. Ideas?

filtering data to include values only if x and y exist
 

Hans,

Insert another column into your data table, with a formula like

=AND(x<"",y<"")

like

=AND(C2<"",D2<"")

(Copied down to match your table)

The formula will return FALSE if either cell is blank, and TRUE only if both are non-blank.

Then use that column as either a page or row field for your pivot table.

HTH,
Bernie
MS Excel MVP


"Hans" wrote in message
...
Excel 2003
Hope somebody can help with the following. I have information x and y in the
same column concerning A (persons)in a pivot table. I would like to show
information x and y per person only if x and y both have a value. If x or y
doesn't have a value then the person in question should not be shown in the
report. I am not sure if pivot table is the best tool for this, but I do not
know of any other better tool. Ideas?

filtering data to include values only if x and y exist
 

Thanks for the help. I will try it out.

"Bernie Deitrick" wrote:

Hans,

Insert another column into your data table, with a formula like

=AND(x<"",y<"")

like

=AND(C2<"",D2<"")

(Copied down to match your table)

The formula will return FALSE if either cell is blank, and TRUE only if both are non-blank.

Then use that column as either a page or row field for your pivot table.

HTH,
Bernie
MS Excel MVP


"Hans" wrote in message
...
Excel 2003
Hope somebody can help with the following. I have information x and y in the
same column concerning A (persons)in a pivot table. I would like to show
information x and y per person only if x and y both have a value. If x or y
doesn't have a value then the person in question should not be shown in the
report. I am not sure if pivot table is the best tool for this, but I do not
know of any other better tool. Ideas?

filtering data to include values only if x and y exist
 

On Nov 2, 5:32 am, Hans wrote:
Excel 2003
Hope somebody can help with the following. I have information x and y in the
same column concerning A (persons)in a pivot table. I would like to show
information x and y per person only if x and y both have a value. If x or y
doesn't have a value then the person in question should not be shown in the
report. I am not sure if pivot table is the best tool for this, but I do not
know of any other better tool. Ideas?

You can filter column x and column y to display non-blanks. Use Data-
Filter-AutoFilter with your whole range selected. Then, use the last
option in the drop down for columns x and y, which is (NonBlanks).

filtering data to include values only if x and y exist
 

Thanks for the good tips. However,x and y are in the same column. This means
if the name of the person Smith is in column A1, information x is in column
B1. Smith is also in A2 and y is in B2. The value is in a third column C. If
person Smith has x and y then I want it to show values in column C, otherwise
the value should not be shown.

"iliace" wrote:

On Nov 2, 5:32 am, Hans wrote:
Excel 2003
Hope somebody can help with the following. I have information x and y in the
same column concerning A (persons)in a pivot table. I would like to show
information x and y per person only if x and y both have a value. If x or y
doesn't have a value then the person in question should not be shown in the
report. I am not sure if pivot table is the best tool for this, but I do not
know of any other better tool. Ideas?

You can filter column x and column y to display non-blanks. Use Data-
Filter-AutoFilter with your whole range selected. Then, use the last
option in the drop down for columns x and y, which is (NonBlanks).