Hi

I have a spreadsheet with unique account numbers in column A, and then
across the top, from Column B onwards, I have listed the next 72 months (Jun
09, Jul 09, etc).

In each row (ie for each account), a forumula in each cell (under each month
for each account) either returns a positive number or 0.

I need to identify the month each account has the positive number (it is the
maturity value), and have the maturity month listed next to the account
number.

I have no idea where to start. Can anyone help?

Insert a new col B. Your 72 col headers are now in C1:BV1
Put in B2, normal ENTER:
=INDEX(C$1:BV$1,MATCH(TRUE,INDEX(C2:BV20,),0))
Copy down to return the required results

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Totteridge Ram" wrote:
I have a spreadsheet with unique account numbers in column A, and then
across the top, from Column B onwards, I have listed the next 72 months (Jun
09, Jul 09, etc).

In each row (ie for each account), a forumula in each cell (under each month
for each account) either returns a positive number or 0.

I need to identify the month each account has the positive number (it is the
maturity value), and have the maturity month listed next to the account
number.

I have no idea where to start. Can anyone help?

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Replace 123 with your unique account number or reference it to a cell. For
testing I have used 123. Tere are 4 occurance of 123. The formula will match
the row with the account number; then find the first value which is more than
0 in that row and then return the month name from the 1st row..Try and
feedback

=INDEX(1:1,MIN(IF(INDIRECT("B" & MATCH(123,A:A,0)&":BU" &
MATCH(123,A:A,0))0,COLUMN(INDIRECT("B" & MATCH(123,A:A,0)&":BU" &
MATCH(123,A:A,0))))))

If this post helps click Yes
---------------
Jacob Skaria


"Totteridge Ram" wrote:

Hi

I have a spreadsheet with unique account numbers in column A, and then
across the top, from Column B onwards, I have listed the next 72 months (Jun
09, Jul 09, etc).

In each row (ie for each account), a forumula in each cell (under each month
for each account) either returns a positive number or 0.

I need to identify the month each account has the positive number (it is the
maturity value), and have the maturity month listed next to the account
number.

I have no idea where to start. Can anyone help?

Thank you so much Max & Jacob - both work. This is fantastic - I had visions
of typing in maturity month manually!!

Brilliant.

"Jacob Skaria" wrote:

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"

Replace 123 with your unique account number or reference it to a cell. For
testing I have used 123. Tere are 4 occurance of 123. The formula will match
the row with the account number; then find the first value which is more than
0 in that row and then return the month name from the 1st row..Try and
feedback

=INDEX(1:1,MIN(IF(INDIRECT("B" & MATCH(123,A:A,0)&":BU" &
MATCH(123,A:A,0))0,COLUMN(INDIRECT("B" & MATCH(123,A:A,0)&":BU" &
MATCH(123,A:A,0))))))

If this post helps click Yes
---------------
Jacob Skaria


"Totteridge Ram" wrote:

Hi

I have a spreadsheet with unique account numbers in column A, and then
across the top, from Column B onwards, I have listed the next 72 months (Jun
09, Jul 09, etc).

In each row (ie for each account), a forumula in each cell (under each month
for each account) either returns a positive number or 0.

I need to identify the month each account has the positive number (it is the
maturity value), and have the maturity month listed next to the account
number.

I have no idea where to start. Can anyone help?

Delighted to hear. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Totteridge Ram" wrote in message
...
Thank you so much Max & Jacob - both work. This is fantastic - I had
visions
of typing in maturity month manually!!

Brilliant.