Can you Create an Autofilter that goes over 2 columns at the same

[Abdul]

Autofilter can be used on a single column or a single row, but what I want
is, to use autofilter to view items that are repeated in two separate columns
that are located next to each other.
example:
# name 1st slot 2nd slot
1 Bob item#4 item#3
2 John item#9 item#4
3 Marry item#18 item#9

in the above example, if I place autofilter on the third column [2nd slot]
and I select [item#9] from the list, it'll filter the entries and show
[Marry] because she's the only person having [item#9] in her [2nd slot]
column, but it will not show that [John] has the same item in the second
column [1st slot].
what I want now, is to create a filter that will show the list of all items
in possession of any member regardless if its in his 1st or 2nd slot. so that
if for example I select [item#4] in the filter, it'll show [Bob] and [John]
but will hide [Marry] because she doesn't possess [item#4].

please help if you know, or at least provide hints/tips or if you know a
guide that help me learn more about this matter.
thanks, Abdul

 

[ShaneDevenshire]

Hi,

Hi the usual way to do this is to use advanced filter however...

1. Create a new column call it Either and in the second cell enter the formula
=If(OR(B2=$H$1,C2=$H$1),True,False)
fill this formula down the column and enter the item# or name you want
located in H1 (or whatever cell you want)
2. Turn on AutoFilter and filter on the Either column for Equal to True.

If this helps, please click the Yes button.