Need Help in Formula or Macro Code on MS Excel

[haka007]

Hello Members,
I got a problem like this

http://imageshack.us/photo/my-images/69/twoproblem.png/

Now my query is in those two files there is a column named "Expiry Date". Now, how can I change that Month & Year also to I,II,III... with their respective names, like whichever is the nearest month including current month in GOLD or any commodity name will named like GOLD-I,GOLD-II.. so on.
I try this with Excel's find and Replace function, like find 9/**/2011 and replace with -I, and then add this number with their name. Many of them replaced successfully.

Like this:-
http://imageshack.us/photo/my-images/90/fmcx01.png/

But some of them changed wrongfully. Ex:- GOLD, GOLDM, SILVER, SILVERM.

Like this:-
http://imageshack.us/photo/my-images/847/fmcx02.png/

The GOLD-II should be change to GOLD-I, because there is no other contract before OCTOBER 2011

Please help me regarding this problem. Waiting for any good man's Help.

And if anyone want to use the original files of these images then please go to these links.

1) http://www.4shared.com/file/19uQ1PRr/09-22-2011ncdex__1_.html

2) http://www.4shared.com/file/jPapW0ua/19092011mcx__1_.html

Thanks in Advance
haka

 

[niceguy_jay]

I have read it over and over but I am afraid others are finding the same what I am finding...Your question is not clear..

What do you mean by "change that Month & Year also to I,II,III... with their respective names, like whichever is the nearest month including current month in GOLD or any commodity name will named like GOLD-I,GOLD-II.. so on."

You need to be more elaborate on this....

 

[haka007]

Hello there,
Thanks for reading my post and sorry for my ignorance in excel, so I can not much elaborate in written words. So I think I post some images what I exactly need. I think it elaborates much than earlier post.

1) My first Image:-

2) My second Image :-

3) My third image :-

IN THIS COL C DATE IS NOT A FACTOR, ONLY FACTOR IS MONTH AND YEAR ON WHICH IT DEPENDS TO CONVERT I, II, III, IV and so on


Now some of them are changed successfully, but some not. Like this...

4) My 4th image :-

Because their expiry month are like this OCT 2011, DEC 2011, FEBRUARY 2012, APRIL 2012.

And those who changed successfully their expiry month are like this SEP 2011, OCT 2011, NOV 2011, DEC 2011

Please help on this issue.
Thanks and Regards

 

[niceguy_jay]

I am only able to understand the third image where you need I,II,III,IV,V,VI etc in column C...for this case I have built up a small formula...check it this works...

Suppose your data
<table border="0" cellpadding="0" cellspacing="0" width="81"><col style="width: 61pt;" width="81"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 61pt;" width="81" height="20">ALMOND-I</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ALMOND-II</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ALMOND-III</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ALMOND-IV</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ALMOND-V</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ALMOND-VI</td> </tr> </tbody></table>etc

are in column A you can put this formula in column C..

=MID($A1,FIND("-",$A1)+1,5)

and copy paste it till the end of column C...see if this helps