Returning a column reference for a data point
 

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?

Returning a column reference for a data point
 

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?

Returning a column reference for a data point
 

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?

Returning a column reference for a data point
 

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?

Returning a column reference for a data point
 

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.