| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Look Up Problem
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!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Look Up Problem
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!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Look Up Problem
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!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Look Up Problem
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!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Look Up Problem
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple, yet complex problem! Using results as new data during calculations? | Excel Worksheet Functions | |||
| Excel Startup Problem | Excel Discussion (Misc queries) | |||
| Problem with MS Community Newsgroups? | Excel Discussion (Misc queries) | |||
| Complex data comparisson and entry problem | Excel Discussion (Misc queries) | |||
| Importing XML containing Complex Elements | Excel Discussion (Misc queries) | |||
Hybrid Mode
