locked
change work center cod RRS feed

  • Question

  • Hi!

    In the example table, I need that in cases in which an employee connects on the same day in more than one work center, replace the number of the center in which he is less time for the one that is longer. I need it because in my real table I have more than 49.000 records

    Thanks in advance

    center_id date time action user_id
    1 02/03/2020 9:12:54 AM out 1
    1 02/03/2020 9:22:18 AM in 1
    4 02/03/2020 10:59:21 AM in 1
    4 02/03/2020 2:55:08 PM out 1
    1 02/03/2020 8:03:28 AM in 2
    1 02/03/2020 3:25:55 PM out 2
    5 04/02/2020 9:12:54 AM in 10
    5 04/02/2020 10:59:21 AM out 10
    140 04/02/2020 2:01:22 PM in 10
    140 04/02/2020 3:32:09 PM out 10

    Thursday, December 17, 2020 7:33 PM

Answers

  • Assumed for every user_id first in will come and in next row out will come.

    Assumed when a user_id has "in" on a date he also has "Out" same day.

    If you apply to 49000 cells then it may be slow because formula needs to check all cells to find the maximum duration of stay.

    Sample file uploaded.

    https://we.tl/t-dFId7DkRgO

    Data is in A1:E11. F and G column is helper column. H is result.

    Say you have data from A1:E501. Then copy F2:H3 upto F3:H501


    Best Regards, Asadulla Javed

    • Marked as answer by rafa_c Monday, December 21, 2020 8:42 AM
    Friday, December 18, 2020 2:38 PM
    Answerer

All replies

  • Can you share a sample how the output you want ?

    Best Regards, Asadulla Javed

    Friday, December 18, 2020 6:48 AM
    Answerer
  • hi Asadulla

    Thanks for your reoly!

    The output should be

    center_id date time action user_id
    4 02/03/2020 9:12:54 AM out 1
    4 02/03/2020 9:22:18 AM in 1
    4 02/03/2020 10:59:21 AM in 1
    4 02/03/2020 2:55:08 PM out 1
    1 02/03/2020 8:03:28 AM in 2
    1 02/03/2020 3:25:55 PM out 2
    5 04/02/2020 9:12:54 AM in 10
    5 04/02/2020 10:59:21 AM out 10
    5 04/02/2020 2:01:22 PM in 10
    5 04/02/2020 3:32:09 PM out 10

    As you can see, for user 1 and 10 the center_id where he has been the least time has changed for the one that has been the longest

    Thanks for your time

    Regards

    Friday, December 18, 2020 8:49 AM
  • Assumed for every user_id first in will come and in next row out will come.

    Assumed when a user_id has "in" on a date he also has "Out" same day.

    If you apply to 49000 cells then it may be slow because formula needs to check all cells to find the maximum duration of stay.

    Sample file uploaded.

    https://we.tl/t-dFId7DkRgO

    Data is in A1:E11. F and G column is helper column. H is result.

    Say you have data from A1:E501. Then copy F2:H3 upto F3:H501


    Best Regards, Asadulla Javed

    • Marked as answer by rafa_c Monday, December 21, 2020 8:42 AM
    Friday, December 18, 2020 2:38 PM
    Answerer
  • Hi Asadulla!

    Thank you for your help.

    I'm sure your solution is perfect, but unfortunately I cannot access the link you indicate.

    I really appreciate your help and your time.


    • Edited by rafa_c Monday, December 21, 2020 8:49 AM
    Monday, December 21, 2020 8:46 AM
  • Hi

    Check below link.

    https://1drv.ms/x/s!Ao913KTUSrrUgRkkCdAQiVuYo5qE?e=HJqCMu

    https://1drv.ms/x/s!Ao913KTUSrrUgRkkCdAQiVuYo5qE?e=4CU1xN


    Best Regards, Asadulla Javed

    Monday, December 21, 2020 12:45 PM
    Answerer
  • You got it!!

    It's really a perfect formula that solve my problem!

    Thanks for sharing your knowledge

    Best regards

    Monday, December 21, 2020 5:09 PM