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...
.
|