Auto Filtering?

[dkenebre]

If I have a chart with several rows of 3 digit combinations in columns
A, B and C and I wanted to have auto filter to show only those rows
that have 2 of the 3 digits that are alike, how could I do that?
The auto filter option does not allow you to do this.

example:
ABC
589
303 Show only
115 Show only
902
088 Show only
476

 

[Ken Wright]

One way, in a helper column next to your data, assuming your data is in Col A
starting A1, in B1 put the following formula and copy down:-

=OR(LEFT(A1)=MID(A1,{2,3},1),MID(A1,2,1)=RIGHT(A1))

Now just filter on Col B for TRUEs

 

[Ken Wright]

Same thing, different formula:-

=SUM(IF(MID(A1,{1,1,2},1)=MID(A1,{2,3,3},1),1,0))>0

 

[dkenebre]

This is a great approach but my 3 digits are not one column but in 3
colunms (A, B, and C)

 

[Peo Sjoblom]

That makes it easier, in a help column use

=SUMPRODUCT(1/COUNTIF($A2:$C2,$A2:$C2))<>3

copy down as long as needed and filter on TRUE

 

[dkenebre]

Looks Good, but
I would like the filter to be called D rather than True?

 

[Peo Sjoblom]

=IF(SUMPRODUCT(1/COUNTIF($A2:$C2,$A2:$C2))<>3,"D","No")

 

[dkenebre]

Peo Thanks, this works perfectly!