Extract Records from 2 Lists to Create 2 new Lists Based on an Inital Criteria Value

Keystoner

New Member
Joined
Jul 12, 2014
Messages
1
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

C/SNameReg RateOT RateTitle
CLori Story$11.75$17.63Admin
John Brown$12.50$18.75Technician
CJane Doe$12$18Technician
Steve Rogers$11$16.50Agent
STom Jones$13$19.50Sr. Technician

<tbody>
</tbody>











PT Employee List

C/SNameReg RateOT RateTitle
SJon Jones$17$25.50Super Technician
Mary Michaels$14$21Audit
CGlen Baker$13$19.50Technician

<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.63Admin
Jane Doe$12$18Technician
Glen Baker$13$19.50Technician

<tbody>
</tbody>

S List

Tom Jones$13$19.50Sr. Technician
Jon Jones$17$25.50Super Technician

<tbody>
</tbody>
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Have you thought of using the Worksheet_Change Event?

Whenever you change column 1 (C/S) you could either re-create the C and S lists, or append/delete based on the Change to that column
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top