Add numbers

[avinashraste]

Dear all,

Hi, I have a set of companies names. Grouped in a three months data.
For eg. Column A contains company's name and column B contains date. Each company contains three rows for three months. for eg. NTPC for current month. NTPC for next month and NTPC for next to next month. Now I have NTPC in column A with the current date in column B in 2nd row ( First row is a header ). NTPC in column A with next month's date in column B in 3rd row and NTPC in column A with next to next month's date in column B in 4th row.
Now I want to put formula which will give me the result :-

NTPC Current Date
NTPC 1 Next month's Date
NTPC 2 Next to next month's Date.

please guide & help.

Thanks & Regards
Avinash Raste

 

[Krishnakumar]

Hi,

In D1 type NTPC

In D2 and copied down,

=index($b$2:$b$100,match(d$1,$a$2:$a$100,0)+rows($d$2:d2)-row($d$2))

 

[avinashraste]

Dear Krishnakumar,
Thank you very much for your reply. But after pasting your formula, it says " N/A ". And moreover I can not paste it in D1 as its my header row and columns are from A to J. So I want it in column A only. Like NTPC, there are 227 names with three dates ( Current, Next & Next to next month ). So please see if you can help me out.

Thanks & Regards

Avinash Raste

 

[Krishnakumar]

Hi,

Can you post a screen shot of your data using any of the following tools ?

HTML Maker or Excel-Jeanie

 

[avinashraste]

Sure Krishnakant,
I will post the screen shot. Till then see this if you can make out, what I want to explain.

SYMBOL - EXPIRY-DATE - OPEN - HIGH - LOW - CLOSE

NTPC - 28-Apr-11 - 125 - 130 - 124 - 129
NTPC - 26-May-11 - 130 - 135 - 127 - 131
NTPC - 30-Jun-11 - 133 - 136 - 129 -132

This kind of data I have for all the 227 scrips ( Companies ). Now keeping the first name as it is i.e. NTPC for current date which is 28-Apr-11, rest of the 2 lines I want " NTPC 1 " for 26-May-11 & " NTPC 2 " for 30-Jun-11

Hope I have cleared your doubts ?

Thanks & Regards

Avinash Raste

 

[Krishnakumar]

Hi,

Insert a Column before Col A. In A2 and copied down,

=IF(B2<>B1,B2,B2& " "&COUNTIF($B$2:B2,B2)-1)

HTH

 

[avinashraste]

Dear Krishnakumr,

Thanks again. It works. But my problem is not solved as I can not change the format of my sheet as this sheet goes to a software. So If I insert a column and after that if I delete the old one, the new one becomes nil as there is no data, If I am keeping the added column, it does not allow me to put it in the software as the format is changed. So please suggest something which will not add/insert any column or delete any existing column. In short, the sheet should not be changed. I hope it is possible.

Thanks & regards

Avinash Raste

 

[Krishnakumar]

Hi,

Once you put the formulas in Col A, Copy Col A, Edit > Pastespecial > Check on 'Value' > OK. Now Delete Col B.

HTH

 

[avinashraste]

Dear Krishnakant,

Its working. Great. You are terrific. Thanks again & again. this is exectly I wanted.

Thanks & Regards

Avinash Raste