I have a report produced by an analytic system that lists separate accounts
and their relevant information. The data is organized by account number so
the account number will appear at the top of the spreadsheet followed by the
information for the account. The problem is that all of the output is listed
in one spreadsheet. So, I have to find a function or create my own function
that will look for the account number and return specific information that is
listed for that specific account. The following formula gets me close, but it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I will try
to explain it more fully. It is driving me crazy!!

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists separate accounts
and their relevant information. The data is organized by account number so
the account number will appear at the top of the spreadsheet followed by
the
information for the account. The problem is that all of the output is
listed
in one spreadsheet. So, I have to find a function or create my own
function
that will look for the account number and return specific information that
is
listed for that specific account. The following formula gets me close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I will try
to explain it more fully. It is driving me crazy!!

Not quite...it looks like this:
A B C
1:
AccountSummary.................................... .................................................. ....
2: Portfolio : 00000119 - 613887041
on12/29/2006..............................................
3: Manager : RICHARD
UNDERWOOD......................................... ..........................
4:................................................ .................................................. ...................
5:..........................................Holdin gs..Coupon........................................ ...........
6: USTN.................................. 41750 .. 4.88
.................................................. ..
7: AGCY.................................. 14000 .. 5.366
..................................................
8: CORP.................................. 27500 .. 5.337
.................................................. ..

The formula I listed in the post will search for the defined string in the
IF function and return cosecutive values of that defined string. For
instance, if I wanted the USTN Holdings value, the formula would look like
this:

Index(A1:C8,SMALL(IF(A1:C8="USTN ",ROW(A1:C8)),ROW(1:1)),3)
<ctrl<shift<enter (to activate array)

Instead of simply searching for the consecutive values, I need it to search
by the account number at the top of the spreadsheet. Remember there are about
85 different entries like the one I listed in this reply. Some of them will
have the USTN field and others will not which presents a problem with the
above formula because it goes through the spreadsheet and returns consecutive
values. I need a way to search by account number so I can avoid this error.
Thanks a lot for replying because I know I am not the greatest explainer, and
this is a difficult question.





"T. Valko" wrote:

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists separate accounts
and their relevant information. The data is organized by account number so
the account number will appear at the top of the spreadsheet followed by
the
information for the account. The problem is that all of the output is
listed
in one spreadsheet. So, I have to find a function or create my own
function
that will look for the account number and return specific information that
is
listed for that specific account. The following formula gets me close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I will try
to explain it more fully. It is driving me crazy!!

Let me clean that up a little bit (can you tell I am new at this):

A B C
1:........#67886537............................... .........
2:...............................Holdings.......Co upon....
3:.............USTN...........41750...........4.88 ......
4:.............AGCY...........14000..........5.366 .....
5:.............CORP...........27500..........5.337 ....

So, the current formula for holdings would be:

INDEX(A1:C5,SMALL(IF(A1:C5="USTN ",ROW(A1:C5)),ROW(1:1)),3)

Hope that makes more sense...Thanks!

"CJLuke" wrote:

Not quite...it looks like this:
A B C
1:
AccountSummary.................................... .................................................. ....
2: Portfolio : 00000119 - 613887041
on12/29/2006..............................................
3: Manager : RICHARD
UNDERWOOD......................................... ..........................
4:................................................ .................................................. ...................
5:..........................................Holdin gs..Coupon........................................ ...........
6: USTN.................................. 41750 .. 4.88
.................................................. .
7: AGCY.................................. 14000 .. 5.366
.................................................
8: CORP.................................. 27500 .. 5.337
.................................................. .

The formula I listed in the post will search for the defined string in the
IF function and return cosecutive values of that defined string. For
instance, if I wanted the USTN Holdings value, the formula would look like
this:

Index(A1:C8,SMALL(IF(A1:C8="USTN ",ROW(A1:C8)),ROW(1:1)),3)
<ctrl<shift<enter (to activate array)

Instead of simply searching for the consecutive values, I need it to search
by the account number at the top of the spreadsheet. Remember there are about
85 different entries like the one I listed in this reply. Some of them will
have the USTN field and others will not which presents a problem with the
above formula because it goes through the spreadsheet and returns consecutive
values. I need a way to search by account number so I can avoid this error.
Thanks a lot for replying because I know I am not the greatest explainer, and
this is a difficult question.





"T. Valko" wrote:

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists separate accounts
and their relevant information. The data is organized by account number so
the account number will appear at the top of the spreadsheet followed by
the
information for the account. The problem is that all of the output is
listed
in one spreadsheet. So, I have to find a function or create my own
function
that will look for the account number and return specific information that
is
listed for that specific account. The following formula gets me close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I will try
to explain it more fully. It is driving me crazy!!

Some of them will have the USTN field and others
will not which presents a problem

Yes, it does!

Does each account occupy the same number of rows? Does the account number
really have the # sign? Are the account numbers really a string of numbers?
Do any account numbers start with leading 0's?

Biff

"CJLuke" wrote in message
...
Let me clean that up a little bit (can you tell I am new at this):

A B C
1:........#67886537............................... .........
2:...............................Holdings.......Co upon....
3:.............USTN...........41750...........4.88 ......
4:.............AGCY...........14000..........5.366 .....
5:.............CORP...........27500..........5.337 ....

So, the current formula for holdings would be:

INDEX(A1:C5,SMALL(IF(A1:C5="USTN ",ROW(A1:C5)),ROW(1:1)),3)

Hope that makes more sense...Thanks!

"CJLuke" wrote:

Not quite...it looks like this:
A B C
1:
AccountSummary.................................... .................................................. ....
2: Portfolio : 00000119 - 613887041
on12/29/2006..............................................
3: Manager : RICHARD
UNDERWOOD......................................... ..........................
4:................................................ .................................................. ...................
5:..........................................Holdin gs..Coupon........................................ ...........
6: USTN.................................. 41750 .. 4.88
.................................................. .
7: AGCY.................................. 14000 .. 5.366
.................................................
8: CORP.................................. 27500 .. 5.337
.................................................. .

The formula I listed in the post will search for the defined string in
the
IF function and return cosecutive values of that defined string. For
instance, if I wanted the USTN Holdings value, the formula would look
like
this:

Index(A1:C8,SMALL(IF(A1:C8="USTN ",ROW(A1:C8)),ROW(1:1)),3)
<ctrl<shift<enter (to activate array)

Instead of simply searching for the consecutive values, I need it to
search
by the account number at the top of the spreadsheet. Remember there are
about
85 different entries like the one I listed in this reply. Some of them
will
have the USTN field and others will not which presents a problem with the
above formula because it goes through the spreadsheet and returns
consecutive
values. I need a way to search by account number so I can avoid this
error.
Thanks a lot for replying because I know I am not the greatest explainer,
and
this is a difficult question.





"T. Valko" wrote:

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists separate
accounts
and their relevant information. The data is organized by account
number so
the account number will appear at the top of the spreadsheet followed
by
the
information for the account. The problem is that all of the output is
listed
in one spreadsheet. So, I have to find a function or create my own
function
that will look for the account number and return specific information
that
is
listed for that specific account. The following formula gets me
close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and I
will try
to explain it more fully. It is driving me crazy!!

I'll just edit the stuff out.

Just replace the sensitive data with dummy data. If you edit out stuff and
the result is not the true layout then that will defeat the purpose.

Biff

"CJLuke" wrote in message
...
I will get it to you when I find some time on Monday. You are correct...it
is
a confidential file, but I'll just edit the stuff out. I figured out a way
to
fix the rows and columns in the output files where I get the data so I
ended
up using the original formula, and it works well. Nonetheless, it would
still
be awesome to figure out how to do it the way I originally planned. I am
thinking it would be a combination of the offset property after you locate
the account number using the find function. I tried this, but the find
function only works for text. Anyway, I'll send it to you Monday. Thanks
for
all the help, and the continued responses...

C. Luke

"T. Valko" wrote:

Well, in this case I'd need to see the the data layout to determine if
this
can even be done. I have a feeling this is "confidential" data?

If you want to send me a copy of the file I'll see what I can do. If it's
a
"big" file I only need the data in question. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"CJLuke" wrote in message
...
This is what the heading for each acccount looks like followed by the
body
of
the report...

Account Summary
Portfolio : 00000119 - 7859414572 on 12/29/2006
Manager : HARRY POTTER

Value
Portfolio Coupon w/o
Sector Title Rate
Accrued
MORT 0 0
USTN 7859414572 4.88 42,120,368
AGCY 7859414572 5.366 14,177,045
CORP 7859414572 5.337 27,518,116
CASH 7859414572 4.195 908,481
MUNI 0 0
Other SECTOR 0 0
Total 7859414572 5.103 84,724,008

Yes, each account occupies the same number of rows...

No, the account does not have the "#" sign in front of it...

Yes, the account numbers can be strings of numbers, but they can also
be
plain text names. There are also a few accounts that use the "-" sign
(ex.
0523-1).

Yes, some of the accounts start with "0"...

Thanks so much! If I can figure this out, I could die happy...



"T. Valko" wrote:

Some of them will have the USTN field and others
will not which presents a problem

Yes, it does!

Does each account occupy the same number of rows? Does the account
number
really have the # sign? Are the account numbers really a string of
numbers?
Do any account numbers start with leading 0's?

Biff

"CJLuke" wrote in message
...
Let me clean that up a little bit (can you tell I am new at this):

A B C
1:........#67886537............................... .........
2:...............................Holdings.......Co upon....
3:.............USTN...........41750...........4.88 ......
4:.............AGCY...........14000..........5.366 .....
5:.............CORP...........27500..........5.337 ....

So, the current formula for holdings would be:

INDEX(A1:C5,SMALL(IF(A1:C5="USTN
",ROW(A1:C5)),ROW(1:1)),3)

Hope that makes more sense...Thanks!

"CJLuke" wrote:

Not quite...it looks like this:
A B C
1:
AccountSummary.................................... .................................................. ....
2: Portfolio : 00000119 - 613887041
on12/29/2006..............................................
3: Manager : RICHARD
UNDERWOOD......................................... ..........................
4:................................................ .................................................. ...................
5:..........................................Holdin gs..Coupon........................................ ...........
6: USTN.................................. 41750 .. 4.88
.................................................. .
7: AGCY.................................. 14000 .. 5.366
.................................................
8: CORP.................................. 27500 .. 5.337
.................................................. .

The formula I listed in the post will search for the defined string
in
the
IF function and return cosecutive values of that defined string.
For
instance, if I wanted the USTN Holdings value, the formula would
look
like
this:

Index(A1:C8,SMALL(IF(A1:C8="USTN
",ROW(A1:C8)),ROW(1:1)),3)
<ctrl<shift<enter (to activate array)

Instead of simply searching for the consecutive values, I need it
to
search
by the account number at the top of the spreadsheet. Remember there
are
about
85 different entries like the one I listed in this reply. Some of
them
will
have the USTN field and others will not which presents a problem
with
the
above formula because it goes through the spreadsheet and returns
consecutive
values. I need a way to search by account number so I can avoid
this
error.
Thanks a lot for replying because I know I am not the greatest
explainer,
and
this is a difficult question.





"T. Valko" wrote:

Is this what you have:

........Acct # .........Info
1.....1011A..........X
2..........................Y
3..........................Z
4.....3822A..........X
5..........................Y
6..........................Z

Biff

"CJLuke" wrote in message
...
I have a report produced by an analytic system that lists
separate
accounts
and their relevant information. The data is organized by
account
number so
the account number will appear at the top of the spreadsheet
followed
by
the
information for the account. The problem is that all of the
output
is
listed
in one spreadsheet. So, I have to find a function or create my
own
function
that will look for the account number and return specific
information
that
is
listed for that specific account. The following formula gets me
close, but
it
only returns the consecutive occurences of a certain parameter.

INDEX(A1:B7,SMALL(IF(A1:A7=A10,ROW(A1:A7)),ROW(1:1 )),2))

If you have a list like the following in A1:B7:

Ashish 234
Sanjay 334
Pongal 434
Ashish 534
Rajesh 634
Suresh 734
Ashish 834

Please email me if you have no idea what I am talking about and
I
will try
to explain it more fully. It is driving me crazy!!