| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookups in arrays
To Whomever Can Help,
I have a worksheet that tracks ID and date information and column flags on repeating rows, ie. subject A001, 14-Apr-2004, S Flag, B Flag, W2 Flag etc... The problem I have is, that the column flags are collected on consecutive rows with the date as there unique identifier... The existing worksheet looks like this... S B W2 W3 A001 | 14-Apr-04 | X | | | | A001 | 22-Apr-04 | | X | | | A001 | 23-Apr-04 | | | X | X | I would like to combine all the information from this sheet into a second sheet to show the dates beside the flags on the SAME row in order to manipulate the data... S Date B Date W2 Date A001 | X | 14-Apr-04 | X | 22-Apr-04 | X | 23-Apr-04 | I tried to use VLOOKUP but it will not recognize the subsequent rows of the ID as it checks the first row of the subject in the current sheet, ie. for A001, it looks up the flags (lookup_value), loads the information into the new sheet, then quits...therefore not looking at the subsequent rows where the subject has further information... I tried to use concatenation to uniquely identify the row record for the "lookup_value" of the VLOOKUP command working on the sheet (I used the subject and column heading in the concatenation...A001S), but I can't use the same in the "table_array" portion of the function for matching... Can some one help... Thanks in advance... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookups in arrays
Use the offset function with the match function as the first argument.
Andrew Scurrah wrote: To Whomever Can Help, I have a worksheet that tracks ID and date information and column flags on repeating rows, ie. subject A001, 14-Apr-2004, S Flag, B Flag, W2 Flag etc... The problem I have is, that the column flags are collected on consecutive rows with the date as there unique identifier... The existing worksheet looks like this... S B W2 W3 A001 | 14-Apr-04 | X | | | | A001 | 22-Apr-04 | | X | | | A001 | 23-Apr-04 | | | X | X | I would like to combine all the information from this sheet into a second sheet to show the dates beside the flags on the SAME row in order to manipulate the data... S Date B Date W2 Date A001 | X | 14-Apr-04 | X | 22-Apr-04 | X | 23-Apr-04 | I tried to use VLOOKUP but it will not recognize the subsequent rows of the ID as it checks the first row of the subject in the current sheet, ie. for A001, it looks up the flags (lookup_value), loads the information into the new sheet, then quits...therefore not looking at the subsequent rows where the subject has further information... I tried to use concatenation to uniquely identify the row record for the "lookup_value" of the VLOOKUP command working on the sheet (I used the subject and column heading in the concatenation...A001S), but I can't use the same in the "table_array" portion of the function for matching... Can some one help... Thanks in advance... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookups in arrays
Thanks for the reply...
I've tried to use the OFFSET() function with the MATCH function inside but to no avail... I've also tried to use the INDEX() function as well but no luck... I guess my logic is off...I'm trying to index several rows in a list and grab information for successive rows and incorporate into individual records (rows), but with no success... The existing worksheet looks like this... S B W2 W3 A001 | 14-Apr-04 | X | | | | A001 | 22-Apr-04 | | X | | | A001 | 23-Apr-04 | | | X | X | B001 | 05-Feb-04 | X | | | | B001 | 14-Feb-04 | | X | X | X | I would like to combine all the information from this sheet into a second sheet to show the dates beside the flags on the SAME row in order to manipulate the data... S Date B Date W2 Date A001 | X | 14-Apr-04 | X | 22-Apr-04 | X | 23-Apr-04 | B001 | X | 05-Feb-04 | X | 14-Feb-04 | X | 14-Feb-04 | Thanks in advance... Andrew -----Original Message----- Use the offset function with the match function as the first argument. Andrew Scurrah wrote: To Whomever Can Help, I have a worksheet that tracks ID and date information and column flags on repeating rows, ie. subject A001, 14-Apr-2004, S Flag, B Flag, W2 Flag etc... The problem I have is, that the column flags are collected on consecutive rows with the date as there unique identifier... The existing worksheet looks like this... S B W2 W3 A001 | 14-Apr-04 | X | | | | A001 | 22-Apr-04 | | X | | | A001 | 23-Apr-04 | | | X | X | I would like to combine all the information from this sheet into a second sheet to show the dates beside the flags on the SAME row in order to manipulate the data... S Date B Date W2 Date A001 | X | 14-Apr-04 | X | 22-Apr-04 | X | 23-Apr-04 | I tried to use VLOOKUP but it will not recognize the subsequent rows of the ID as it checks the first row of the subject in the current sheet, ie. for A001, it looks up the flags (lookup_value), loads the information into the new sheet, then quits...therefore not looking at the subsequent rows where the subject has further information... I tried to use concatenation to uniquely identify the row record for the "lookup_value" of the VLOOKUP command working on the sheet (I used the subject and column heading in the concatenation...A001S), but I can't use the same in the "table_array" portion of the function for matching... Can some one help... Thanks in advance... . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookups | Excel Discussion (Misc queries) | |||
| Looking up data by row and column (Arrays, Lookups, Index, Match?? | Excel Worksheet Functions | |||
| Lookups or something | Excel Worksheet Functions | |||
| Lookups | Excel Worksheet Functions | |||
| LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Linear Mode
