Looking for Excel Formula

[beadedbytes]

I'm looking for a formula that would successfully do the following example:

Date SOURCEyymmYZ Result
03/19/10 ABCDEF1003GH ABC-1
03/19/10 ABCDEF1003GH ABC-1
03/27/10 ABCDEF1003GH ABC-1
03/27/10 ABCDEF1003GH ABC-1
03/30/10 ABCDEF1003GH ABC-1
04/07/10 ABCDEF1003GH ABC-1
04/07/10 ABCDEF1003GH ABC-1
04/15/10 ABCDEF1003GH ABC-2
04/15/10 ABCDEF1003GH ABC-2
04/15/10 ABCDEF1003GH ABC-2
04/21/10 ABCDEF1003GH ABC-2
04/21/10 ABCDEF1003GH ABC-2
05/15/10 ABCDEF1003GH ABC-2
05/15/10 ABCDEF1003GH ABC-2
05/21/10 ABCDEF1003GH ABC-2
05/21/10 ABCDEF1003GH ABC-2
06/15/10 ABCDEF1003GH ABC-2


SOURCE* column: If 9th-10th digit (mm)=03, then find next month AND day >=15 in DATE column.

The Result column is already pre-populated with ABC-1.

For dates >=(Next Month) AND (Day=15+), change ABC-1 to ABC-2 in Result column.

Thanks in advance for any help.

 

[GlennUK]

With a formula you can't change cell contents that are already there ...

change ABC-1 to ABC-2 in Result column

What you could do is have a new column to the right of you existing data, with this formula:

=IF(MID(B2,9,2)="03",VLOOKUP(DATE(YEAR(A2),MONTH(A2)+1,15),$A$2:$C$2000,3),C2)

to generate the results that you want. If you then want to replace the original results with the new results then copy the new results and paste as values over the old ones.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[beadedbytes]

Sorry for taking so long to response to this thread. I have been trying to modify the suggested formula in order to get the desired outcome.

Here’s a bit more information. Basically, I would like a formula that can return the information that is currently listed in the RESULT column in my example above. When I receive the data file, it only contains the DATE and SOURCE columns only.

Logic and result needed:
* Look in the SOURCE column, find the month located in digits 9-10.
* If month=xx, then find the value in DATE column that is equal to or greater than
* Month in SOURCE column plus one month (e.g. 03+1=04) and day=15 or next largest day after 15.
* When values are found that match this criteria, put in column ABC-2.
* For values that do not meet this criteria, put ABC-1 in column C.

The only other thing to note is that the original data file will have other codes in the SOURCE column that contain other year/month comibinations. Also, it would be easy enough to move the SOURCE column before DATE column.

3/19/2010 ABCDEF1003GH
3/19/2010 ABCDEF1003GH
3/27/2010 ABCDEF1003GH
3/27/2010 ABCDEF1003GH
3/30/2010 ABCDEF1003GH
4/7/2010 ABCDEF1003GH
4/7/2010 ABCDEF1003GH
4/15/2010 ABCDEF1003GH
4/15/2010 ABCDEF1003GH
4/15/2010 ABCDEF1003GH
4/21/2010 ABCDEF1003GH
4/21/2010 ABCDEF1003GH
5/15/2010 ABCDEF1003GH
5/15/2010 ABCDEF1003GH
5/21/2010 ABCDEF1003GH
5/21/2010 ABCDEF1003GH
6/15/2010 ABCDEF1003GH

4/27/2009 ABCDEF0904GH
4/27/2009 ABCDEF0904GH
4/30/2009 ABCDEF0904GH
5/7/2009 ABCDEF0904GH
5/7/2009 ABCDEF0904GH
5/21/2009 ABCDEF0904GH
5/21/2009 ABCDEF0904GH
6/16/2009 ABCDEF0904GH
6/16/2009 ABCDEF0904GH
6/21/2009 ABCDEF0904GH