Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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(COUN TIF($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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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 "Max" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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" wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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, =IF($A2-1,COUNTIF($A2:$A2,K2),"") but think the above revisions should make it a cleaner evaluation for the CF trigger -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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, =IF($A2-1,COUNTIF($A2:$A2,K2),"") but think the above revisions should make it a cleaner evaluation for the CF trigger -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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 "Max" wrote: 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, =IF($A2-1,COUNTIF($A2:$A2,K2),"") but think the above revisions should make it a cleaner evaluation for the CF trigger -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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" wrote: 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, =IF($A2-1,COUNTIF($A2:$A2,K2),"") but think the above revisions should make it a cleaner evaluation for the CF trigger -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Wobbled countif and conditional format Results
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF provides weird results | Excel Worksheet Functions | |||
COUNTIF with pivot table results? | Excel Worksheet Functions | |||
Conditional Sum Argument results do not equal cell results Excel | Excel Worksheet Functions | |||
Multiple Countif results | Excel Worksheet Functions | |||
countif results | Excel Worksheet Functions |