Adding text to a column based on value in another column

[exceluser29]

Hi,

I have two tables in hand:
Table 1: has data showing Customer no, Renewal Month, last_active_date, report_pull_dt and other columns.

Table 2: has Renewal Month and Cancel Dt Limit

What I have to do is add a column in table 1 (Status) based on their last_active_date .

For example, If renewal Month is July and last_active_date < Cancel Dt Limit, then status = 'cancel' else 'Active'.

I will have to lookup the Cancel Dt Limit from table 2, based on their renewal month.

Can someone please help? I was thinking a combination of Vlookup and if statement but dont know how to write!

Thanks!

 

[DonkeyOte]

The specifics of the data types being used are missing so it's hard to give a complete answer I'm afraid... eg:

Are Renewal Month / last_active_date actual date values or simply string values containing the Month Name ?
Same question holds true for Table 2 (both fields) also

Does Table 2 hold a record for every month that will be found in Table 1 ?

Is Table 2 sorted in any way ?

All of the above will dictate to some extent the options open to you.

 

[exceluser29]

Hi

Renewal Month is populated with: Jan, Feb, Mar, Apr,May, June, July --till Dec. Same with Renewal Month in Table2. Last Active date, Cancel Dt Limit are all in date format: dd-mmm-yy.

Table 2 is not sorted, but I can sort it if it helps!

I could have posted a screen shot of my table but don't know how to do it, new user! Yes, table 2 hold records for every month from table 1.

Thanks for your help!

Quote:
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Are Renewal Month / last_active_date actual date values or simply string values containing the Month Name ?
Same question holds true for Table 2 (both fields) also

Does Table 2 hold a record for every month that will be found in Table 1 ?

Is Table 2 sorted in any way ? </TD></TR></TBODY></TABLE>
All of the above will dictate to some extent the options open to you.

 

[exceluser29]

Here's how my tables look:

TABLE1
Customer No Renewal Month Last Active Date Report Pull Date CustStat
1 Jan 07/15/2010 07/20/2010
2 Feb 06/13/2010 07/20/2010

TABLE2

Renewal Month Cancel Dt Limit
Jan 03/15/2010
Feb 04/15/2010
Mar 05/15/2010

What I need to do is define whether CustStat is cancel or active. If renewal month is Jan and if they have been active till cancel dt limit (Last Active Date > Cancel Dt limit) that means they are active else Cancel.

Thanks!

 

[DonkeyOte]

I guess the point was more to do with Renewal Month - storing as a string rather than a date will lead to some confusion when it comes to 2011, no ?
Or do you have one file per calendar year ?

 

[exceluser29]

You guessed right; I have one file per calendar year (as they are huge files!)

Thanks.

 

[DonkeyOte]

OK - final question (I hope) - you refer to TABLE1 & TABLE2 - are you using XL2007+ Tables specifically ?

 

[exceluser29]

No, I am using Excel 2003! My company is rich, yet primitive!

 

[DonkeyOte]

OK. See if the below is along the lines of what you had in mind (?)

Excel Workbook
	A	B	C	D	E
1	Customer No	Renewal Month	Last Active Date	Report Pull Date	CustStat
2	1	Jan	15-Jul-10	20-Jul-10	Active
3	2	Feb	13-Jun-10	20-Jul-10	Active
4	3	May	13-Jun-10	20-Jul-10	Cancel
Sheet1

Excel Workbook
	A	B
1	Renewal Month	Cancel Dt Limit
2	Jan	15-Mar-10
3	Feb	15-Apr-10
4	Mar	15-May-10
5	Apr	15-Jun-10
6	May	16-Jul-10
7	Jun	14-Aug-10
8	Jul	14-Sep-10
9	Aug	14-Oct-10
10	Sep	14-Nov-10
11	Oct	14-Dec-10
12	Nov	14-Jan-11
13	Dec	14-Feb-11
Sheet2

Note: I've assumed the Cancel Limit isn't always the 15th of the month 2 months after renewal month - if it is you don't really need the lookup table.

 

[exceluser29]

Thanks for your prompt reply! Yes, Cancel Dt Limit is always 2 months after Renewal Month.

I should have pointed out that Report Pull Date also plays a role in this. Sorry! This works great for cases when :
Renewal Month < Report Pull date

For cases,
when Renewal Month > Report Pull Date, even when Last Active date < Renewal Month, they can be active as the report was pulled before their renewal month.

For example, If Renewal Month = Nov, Cancel Dt Limit is 14-Jan-2011. So, If Last Active date is 20-Jul-10, they are still active as that's report pull date too. How do I incorporate this part?

Hope I am explaning it clearly.

Thanks for your help!