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
Ive attached a copy of my spreadsheet for reference.
The second part Ill 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 Ill 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 lets 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 hes competency (C15) to 11-May-2011.
5th course on the 09-Jul-2010 (B17) so the system increased hes competency (C15) to 09-Jul-2011.
6th course on the 05-Nov-2010 (B18) so the system increased hes 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 lets 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 Ive 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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.