I know I need to use a combinaion of MATCH, VLOOKUP, maybe INDEX, to accomplish this, but need some help configuring the array formula(s).
I have a list of Full-time employees, their titles and rates. I have another list of Part-time employees with their corresponding titles and rates. The lists will update as employee's status changes - this is done manually, not a problem there. What I am looking to do is copy their associated records to new lists depending on a text character I type in a column preceeding their record - what I am calling the "C/S" column.
FT Employee List
<tbody>
</tbody>
PT Employee List
<tbody>
</tbody>
In the sample lists above, I want to be able to type a C or S in front of their record (row) and then have that data copy over (not including the cells with the C or S) and fill in to a seperate list of all those with C's and another unique list with all those with S's. The forumla would need to skip blanks in the C/S column and remove records from the new dynamic lists if a C or S was removed from the source employee lists. See sample resulting tables below.
C List
<tbody>
</tbody>
S List
<tbody>
</tbody>
I have a list of Full-time employees, their titles and rates. I have another list of Part-time employees with their corresponding titles and rates. The lists will update as employee's status changes - this is done manually, not a problem there. What I am looking to do is copy their associated records to new lists depending on a text character I type in a column preceeding their record - what I am calling the "C/S" column.
FT Employee List
C/S | Name | Reg Rate | OT Rate | Title |
C | Lori Story | $11.75 | $17.63 | Admin |
John Brown | $12.50 | $18.75 | Technician | |
C | Jane Doe | $12 | $18 | Technician |
Steve Rogers | $11 | $16.50 | Agent | |
S | Tom Jones | $13 | $19.50 | Sr. Technician |
<tbody>
</tbody>
PT Employee List
C/S | Name | Reg Rate | OT Rate | Title |
S | Jon Jones | $17 | $25.50 | Super Technician |
Mary Michaels | $14 | $21 | Audit | |
C | Glen Baker | $13 | $19.50 | Technician |
<tbody>
</tbody>
In the sample lists above, I want to be able to type a C or S in front of their record (row) and then have that data copy over (not including the cells with the C or S) and fill in to a seperate list of all those with C's and another unique list with all those with S's. The forumla would need to skip blanks in the C/S column and remove records from the new dynamic lists if a C or S was removed from the source employee lists. See sample resulting tables below.
C List
Lori Story | $11.75 | $17.63 | Admin |
Jane Doe | $12 | $18 | Technician |
Glen Baker | $13 | $19.50 | Technician |
<tbody>
</tbody>
S List
Tom Jones | $13 | $19.50 | Sr. Technician |
Jon Jones | $17 | $25.50 | Super Technician |
<tbody>
</tbody>