filtering data to include values only if x and y exist

[Guest]

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?

 

[Bernie Deitrick]

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

 

[Guest]

Thanks for the help. I will try it out.

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

 

[iliace]

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

 

[Guest]

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.