Wobbled countif and conditional format Results

[Luke]

In sheet2 there is raw data spanning F2:CQ8359.
In sheet1 are the key ranges (no range named). A2:A8359 (exact order) and
B2:F8359 (concatenations of exact order - less exact order)

i.e. if A2 = ABC then B2:F2 = ACB, BAC, BCA, CAB, CBA repectfully.

I need to display in sheet1 G6:CV8359 any cells in sheet2!F2:CQ8359 that
match in exact order as in $A$2:$A8359 Or if it is not exact order then chec
$B$2:$F8359 otherwise leave it blank ("").

Therefore in sheet1 at G6 this is what I use
=IF(COUNTIF($A$2:$A10,Sheet2!F6),Sheet2!F6,IF(COUNTIF($B$2:$F10,Sheet2!F6),Sheet2!F6,"")).
Noting that G6 is the center cell of 9 total cells/rows to check (Row G6
then 4 rows before and 4 rows after).

I see the correct results with that formula.

I then creat a Conditional format - 1st highlighting Sheet1 K2:CV8359, then
Format; Conditional Format; entering the =COUNTIF($A$2:$A2,K2) to color
"Green" only those that are in exact order, then add a 2nd condition of
=COUNTIF($B$2:$F2,K2) to color "Yellow" only those that are in concatenation
of exact order (less exact order).

The issue I am having is when I do the conditional format I get some of the
cells that are exact highlight yellow and some of the non exact cells
highlight Green and some of the cells don't highlight at all.

I don't get why it does that nor can I figure out what to do.

I have checked in with websites like
http://www.contextures.com/xlCondFormat03 and also here at communities but
come up short with answers.

I hope to have included enough information for you to savey.
As always I am greatful for your help.

Regards,
Luke

 

[Max]

Can you upload your sample* and post a link to it here?
*desensitized as appropriate

You could use:
http://www.freefilehosting.net/

Copy the direct link which is generated after you upload your sample,
then paste it into your reply here

 

[Luke]

I sure will give it a try! I just returned from a short trip so I will breif
the darn thing to ya here soon.
Thanks Max

 

[Luke]

Here it is Max. It took longer to transform than I expected.

http://www.freefilehosting.net/download/3ehka
You should note that the top A2 and bottom row of information holds the
functions/formula. the rest are just data values.
The conditional format is in Sheet1.
I will be watching for questions you might have.
Thanks,
Luke

 

[Luke]

Max, Naturally I found errors after I sent the first "Test" file.
Here is "Test2" file direct link.

http://www.freefilehosting.net/download/3ei11

Regards,
Luke

 

[Max]

Try amending the CF in Sheet1 to this

With K2 as active cell

Cond1: =AND($A2>-1,$A2=K2)
Format: green

Cond2: =COUNTIF($B2:$F2,K2)>0
Format: yellow

I'm not sure if there's some ambiguity in your original cond1, re:

=IF($A2>-1,COUNTIF($A2:$A2,K2),"")

but think the above revisions should make it a cleaner evaluation for the CF
trigger

 

[Max]

Try amending the CF in Sheet1 to this

With K2 as active cell

Cond1: =AND($A2>-1,$A2=K2)
Format: green

Cond2: =COUNTIF($B2:$F2,K2)>0
Format: yellow

I'm not sure if there's some ambiguity in your original cond1, re:

=IF($A2>-1,COUNTIF($A2:$A2,K2),"")

but think the above revisions should make it a cleaner evaluation for the CF
trigger

 

[Luke]

It is cleaner but still not getting the intended results. It is curious that
such a straight forward approach doesn't produce.

in the mean time I will keep kicking it. I will watch here for or repost
with new ideas.

Luke

 

[Luke]

Max, The apparent problem was the fact that because I needed results from
range $a2:$A10 = k6 (4 cells/rows before and 4 cells/rows after K6), k2 was
renderd into a forward search only and would not look back, meaning that CF
was only looking A2:a10,k2 respectively.

What I did was changed my selection for CF starting in K6:CV14, then entered
the following. Also I beat all the CF formulas around to get the following.

I am happy with this but if you know a nice and quick formula please do tell.

Cond1: =IF(SUMPRODUCT(--ISNUMBER(MATCH($A2:$A10,K6,0))),(K6)+0,"")
Format: green

Cond2: =IF(SUMPRODUCT(--ISNUMBER(MATCH($B2:$F10,K6,0))),(K6)+0,"")

SEE http://www.freefilehosting.net/download/3fg7a for the outcome.

Thank you for your diligence,
Luke

 

[Max]

Luke,

I am happy with this ..

I'd think it's best to just go with what one's happy with (it works just the
way you want it to, you understand what's happening, and you're able to
quickly cross-apply it to any other similar scenario)