PDA

View Full Version : Solved: Check cell string in column



CCkfm2000
12-20-2010, 08:09 AM
Hi and happy holidays,

This is going to be in two parts.

The first is with a formula in column “I”, this is currently what I have

=IF(OR(G14=G13,G14=G12,G14=G11,G14=G10,G14=G9,G14=G15,G14=G16,G14=G17,G14=G 18,G14=G19,G14=H15,G14=H14,G14=H13,G14=H12,G14=H11,G14=H10,G14=H9,G14=H16,G 14=H17,G14=H18,G14=H19),"ok","not")

What I need is to check the cell string in say cell “G12” i.e. “G2” to “G11” and “G13” to “G” what every the last cell in this column also column “H”

And then cell “G13” i.e. “G2” to “G12” and “G14” to “G” what every the last cell in this column also column “H”

And on and on till the end

I’ve attached a copy of my spreadsheet for reference.

The second part I’ll follow soon.

Thanks

Celelond
12-20-2010, 08:57 AM
I think you missing an attachment :x

CCkfm2000
12-20-2010, 11:01 AM
sorry here it is....:banghead:


snow all over!!!!! :cloud9:

Bob Phillips
12-20-2010, 11:41 AM
Try

=IF(COUNTIF($G$2:$G$42,G2)+COUNTIF($H$2:$H$42,G2)>1,"ok","not")

CCkfm2000
12-22-2010, 09:35 AM
Thanks xld

I'm not sure of how to explain the next problem so I've attached another file for you to look at with examples.

Bob Phillips
12-22-2010, 10:22 AM
Tell us why J4 should be ok and why J21 is correct in saying not.

CCkfm2000
12-22-2010, 01:16 PM
Ok I’ll try an explain

The rows in green are when people do a course the system than give that person a competency which is in purple.

In this example let’s take employee 20416541

He did the 1st course on the 23-Feb-2009 (B11) so the system gave him a competency starting 23-Feb-2009 (B13) and expires on the 23-Feb-2010 (C12), which has expired.
2nd course on the 12-May-2009 (B13) which the system failed to give him a competency.
3rd course on the 02-Mar-2010 (B14) so the system gave him a new competency 02-Mar-2010 (B15).
4th course on the 11-May-2010 (B16) so the system increased he’s competency (C15) to 11-May-2011.
5th course on the 09-Jul-2010 (B17) so the system increased he’s competency (C15) to 09-Jul-2011.
6th course on the 05-Nov-2010 (B18) so the system increased he’s competency (C15) to 05-Nov-2011.
The 4th and 5th competency is with the 6th competency, so we need to ignore them.
-------------------------------------------------------------------------------------------------------------------------------
In this example let’s take employee 10224916

He did the 1st course on the 22-Oct-2009 (B19) so the system gave him a competency starting 22-Oct-2009 (B20) and expires on the 23-Oct-2010 (C20).
2nd course on the 15-Dec-2010 (B21) but the system failed to increase the competency so the status is NOT (J21) which is correct.

-------------------------------------------------------------------------------------------------------------------------------
I rows “G” and “H” I’ve combine the employee numbers with the date without the Day, so this makes the checking easier.
-------------------------------------------------------------------------------------------------------------------------------
I hope this explains.

Bob Phillips
12-22-2010, 02:30 PM
Wow! That is an explanation and a half.

Try this array formula

=IF($B3<=MAX(IF($A$2:$A$25=A3,$C$2:$C$25)),"ok","not")

CCkfm2000
12-23-2010, 08:36 AM
Thanks xld,

with the first formula and the second it's working good

=IF(COUNTIF($G$2:$G$65536,G2)+COUNTIF($H$2:$H$65536,G2)>1,"ok",IF($B3<=MAX(IF($A$2:$A$65536=A3,$C$2:$C$65536)),"ok","not"))

my next question is on the date,

if I have a date in cell "C2" e.g. 24-Mar-2011 how do I with a formula change it to 23-Mar-2010? :whistle:

Bob Phillips
12-23-2010, 09:58 AM
=DATE(YEAR(C2)-1,MONTH(C2),DAY(C2)-1)

CCkfm2000
12-25-2010, 01:02 AM
Thanks xld,

Happy holidays.

:ole: