Need formula to show staff, appearing at multiple sites

[Darth269]

Hi all,

I've tried google and Forum searches, but i'm struggling to find something that I understand and that does what I need.

I have a table in sheet 2 that has data showing which staff work in which sites. (over 54000 rows). Staff Unique ID is in B, and Site is in I.

What I've done is pulled out the unique staff id's (just over 21000), and I'm hoping in my new table, to flag any staff Id's that work at more than one site...

The site names are all unique.
there are expected to be entries where a staff member (rowB) has entries in the same site(rowI) more than once, this can be ignored (i.e. STAFF2 works in SITE 1, SITE 1, SITE 1, this is fine)
I need to flag the ID's (rowB) that appear in SITE 1(rowI) and SITE 2 (and maybe SITE 3 too)

Can anyone guide me towards (or provide if you're feeling really special), the code, formula or function I would need to accomplish this?

I'm sorry that I can't provide sample data... and hope I've explained it well enough here.

[:) Sixthsense :)]

I'm sorry that I can't provide sample data... and hope I've explained it well enough here.

We don't need your actual workbook. We just need a sample file with XYZ of data to understand your data structure and the expected result

[Darth269]

OK, thanks,

I've added some sample data, the 2nd sheet is where i'd like my results, I don't mind if it's a 1 & 0 solution or text...

example.xlsx

[:) Sixthsense :)]

In C2 Cell of STAFF Sheet


=IF(A2="","",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=0,"No Site Worked",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=1,"Single",IF(COUNTIF(TASK_PARENT_ONLY!I:I,VLOOKUP(A2,TASK_PARENT_ONLY!B:I,8,0))<>COUNTIF(TASK_PARENT_ONLY!B:B,A2),"Multiple","Single"))))


Drag it down...

[Darth269]

In C2 Cell of STAFF Sheet


=IF(A2="","",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=0,"No Site Worked",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=1,"Single",IF(COUNTIF(TASK_PARENT_ONLY!I:I,VLOOKUP(A2,TASK_PARENT_ONLY!B:I,8,0))<>COUNTIF(TASK_PARENT_ONLY!B:B,A2),"Multiple","Single"))))


Drag it down...

Oh man I really appreciate your help on this, but (and i'm sorry if I didn't explain it right), this shows staff as Multiple, if they appear multiple times, whereas, I'm looking for it to show multiple, if their multiple entries after not the same... so someone might have 10 entries, all at the same site, but it's the ones that have different sites showing that I want to highlight...

Sorry.... (and thanks!)

[:) Sixthsense :)]

this shows staff as Multiple, if they appear multiple times, whereas, I'm looking for it to show multiple, if their multiple entries after not the same(and thanks!)

That is how the formula is created and it's working in the same way as you described above.

Please check and confirm

[Darth269]

That is how the formula is created and it's working in the same way as you described above.

Please check and confirm

Hi Sixthsense,

I'm sure this is just me explaining it badly, and for that i'm sorry, but this formula is counting the number of times a staff member appears, and flagging them as multiple if they appear more than once.

What I need, is a formula that looks for a staff member that has different sites listed against them in column I.

If you use the example sheet, and change Kirsty's SITE 2 entry to be Site one, the Staff sheet should change to say single, whereas it does not, because it's looking at how many times the person appears.

[:) Sixthsense :)]

Oopss.. sorry for that... Here is the revised formula.

In C2 Cell of STAFF Sheet


=IF(A2="","",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=0,"No Site Worked",IF(COUNTIF(TASK_PARENT_ONLY!B:B,A2)=1,"Single",IF(COUNTIFS(TASK_PARENT_ONLY!B:B,A2,TASK_PARENT_ONLY!I:I,VLOOKUP(A2,TASK_PARENT_ONLY!B:I,8,0))<>COUNTIF(TASK_PARENT_ONLY!B:B,A2),"Multiple","Single"))))


Drag it down...

[Darth269]

Ahhhh!

Nice one, this got it! Thank you soo much.