excel index and match

[jimjohn]

hi sorry to bother you i just had a question regarding excel and was wondering if you could help out.

I have a spreadsheet employee file with the following columns:
Transit (which is a unique number)
Title (which is a position title)
and many other columns pertaining to employees
Each transit will have many different people with many different position titles.

I have received another file that has about 900 transits. For each of those transits, I need to find all the employees who's title contains "Manager Customer Service" There may be more than one Manager Customer Service in a given transit, or there could be none in a given transit.

There doesn't seem to be a way for me to use INDEX and MATCH for this, because if I match transits, each transit has many different titles and it will only return my first match.

Any ideas what I can use to do this? thanks a lot.

[BigBas]

This can probably be resolved using a Filter or some variation of an array formula. Post a .zip sample of the file so that we have a better idea of the structure, and what you need done.

[jimjohn]

thx ok here is an example of a smaller version of my excel sheets:

so each transit has many different employees with many different position titles. Sheet 2 contains a list of the transits that i'm interested in. I want a list of all the employee ID's of employees who are from one of the transits listed in sheet 2 and who's position title contains the words "manager customer service". For example, column D in sheet 2 contains what I would want. I can just filter sheet 1 by position titles that contain "manager customer service" and then remove the ones from transits that i am not interested in. But, in the actual file, there are about 30000 employees in sheet1 and i am given 900+ transits in sheet 2 so it would take me ages like that.

any ideas? thanks a lot guys.

[pinmaster]

Hi,
update: just realized that you don't need to create a list since you already have one, (sheet2) so just point the formula to that list!...good luck!

Don't know if this will help but try this:

type the transit numbers you want to delete in a column, then in a column next to your data use something like this:

=IF(AND(COUNTIF(F1:F100,B1)>0,LEFT(C1,24)="manager customer service"),1,"")

copy all the way down, this will put a "1" beside the transit numbers that is in your list of numbers to be checked and that also has a title of "manager customer service", you can then sort by that column or use a filter, now all you have to do is select and delete.

update: just realized that you don't need to create a list since you already have one, (sheet2) so just point the formula to that list!...good luck!

HTH
Jean-Guy