Need to return the worker site, based on a table with his working hours

Hello, everyone

I have a table with the worker's number and his working hours in one or several sites (i attached an example).

I need a formula (array formula, maybe) that returns:

a) if he has worked in just one site: that site (line 2 has the site's name)

b) If he has worked in more than one site: the word "Check"


I think is clear in the example.

I thank you in advance.

Best regards,
BS

Please try in E3 and copy down:

Formula:

Please Login or Register  to view this content.

with Power Query (add-in for XL2013)

Worker number	Result
29	DE.1052
30	DE.1052
31	DE.1053
32	DE.1054
33	DE.1053
52	Check
62	DE.1052
158	Check

Please Login or Register  to view this content.

(not optimized)

Originally Posted by Bruno Trafaria

Hi, Sandy.

Thank you so much.
Actually i will use the formula in a diferent sheet or even a diferent excel file. I think your Power Query is not optimized for that (but i don't understand nothing about Power Query).

Also, the construction sites will continue to grow as we get more contrats (DE.1055, DE.1056, DE.1057...)
Best regards

Power Query is just for different sheets, files even closed files.
btw. why not optimized?
FYI: https://learn.microsoft.com/en-us/power-query/

Hi, Sandy.

Thanks, again

I never wrote "not optimized". It's in your post, i thought you wrote it!
I?ll look at the link you sent me.

Many thanks.

Can you, please, make that adjustment?!

On tabblad sheet with formula you can now try in B3 and copy down:

Formula:

Please Login or Register  to view this content.

It is easiest to do this in 2 steps this way.

Sorry, Hans.

This bad example was my fault.
Actually, i wil not have column E, in Data sheet. That column was the one you created with your 1st atempt to help me.

I am attaching the correct Example file.

Sorry and thanks.

Thanks, Sandy

I was just answering because you wrote: "btw. why not optimized?"

Have a great day!

Sorry Bruno,

I've thought about a solution without a help column in sheet Data,
but then I don't manage to recognize the checks in the right way (especially when Construction site on Data consists of more than 3 columns).

No worries.

You did an amazing job.
Maybe there's no formula to solve it - i?ll check Sandy's solution.

Thanks a lot!!!