+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2210
    Posts
    1,792

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

    Please try in E3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,974

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

    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)
    sandy
    I want to see what you are trying to achieve, not how you are trying to do it
    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    Hi, Hans

    Thanks for the quick reply. Amazing job.

    I just need a final touch!
    I will not use the formula exactly to the right of the data, as you entered it. That's why i need it in column "I", instead of "try in E3 and copy down".

    Can you, please, make that adjustment?!
    Thank you so much

    (i attach a new example, which explains better how/where i will use the formula)
    Attached Files Attached Files
    Last edited by Bruno Trafaria; 09-22-2022 at 06:24 AM. Reason: More information

  5. #5
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    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
    Last edited by Bruno Trafaria; 09-22-2022 at 06:32 AM. Reason: More information

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,974

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

    Quote Originally Posted by Bruno Trafaria View Post
    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/

  7. #7
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    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.

  8. #8
    Valued Forum Contributor HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2210
    Posts
    1,792

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

    Can you, please, make that adjustment?!
    On tabblad sheet with formula you can now try in B3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It is easiest to do this in 2 steps this way.
    Attached Files Attached Files

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be everywhere
    MS-Off Ver
    All on PC except 365
    Posts
    9,974

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

    Quote Originally Posted by Bruno Trafaria View Post
    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.
    mine not optimized means M-code can shorter 3 lines insted of 6, or with shorter lines but it works the same

    have a nice day
    Last edited by sandy666; 09-22-2022 at 07:08 AM.

  10. #10
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    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.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    Thanks, Sandy

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

    Have a great day!

  12. #12
    Valued Forum Contributor HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2210
    Posts
    1,792

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

    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).

  13. #13
    Registered User
    Join Date
    02-19-2019
    Location
    Portugal
    MS-Off Ver
    Microsoft? Excel? 2021 MSO (vers?o 2210 Build 16. 0. 15726. 20068) 32-bit
    Posts
    32

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

    No worries.

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

    Thanks a lot!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP Formula to Update Map and Code based on Site name and site ID
    By gcolegcole42 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2022, 05:21 AM
  2. Sum worker hours by last name only
    By Elirannig in forum Excel General
    Replies: 7
    Last Post: 12-07-2021, 08:01 PM
  3. Return site based on client
    By kris01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2020, 12:16 PM
  4. Calculate total hours worked in week by worker name
    By evgemans in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-23-2019, 12:24 PM
  5. Replies: 3
    Last Post: 06-16-2019, 05:49 AM
  6. Replies: 1
    Last Post: 05-10-2013, 02:03 PM
  7. Total Hours worked per Job or Worker
    By DJohnson2 in forum Excel General
    Replies: 2
    Last Post: 07-19-2012, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1