+ Reply to Thread
Results 1 to 30 of 30

Populate time based on condition from Book 1 to Book 2....

  1. #1
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Populate time based on condition from Book 1 to Book 2....

    Hi,

    I’m looking for some expert advice on being able to solve a problem that I’ve been stuck on for quite some time now…

    Here’s what I have…

    •Book 1 has some data with “Time” field and I need to populate the time for the corresponding entry into Book 2. My required Output is in Column G of Book 2.

    I’ve attempted by concatenating data and trying to use VLOOKUP but that doesn’t seem to be working in the desired way obviously as it only picks the first instance.

    Then I tried to create unique IDs by assigning unique IDs by way of =COUNTIF($D$2:D2,D2) using multiple permutations and combinations, but to no avail… The biggest challenge is that the number of entries between the 2 aren’t the same. For example: Row 7 to 22 in book 1 isn’t the same in number. There are only 3 instances of it in Book 2 Row 4 to Row 6.

    In the above case, there is only one similarity which is the status so I need to pick the maximum time for the status of each entry corresponding in Book 2. Therefore, to help understand I’ve manually picked the Max time of Status “DN” and ignored the rest of the rows for the Same entry (highlighted in black).

    Could you please assist me in solving this “Monster”. Hope the above makes it a little clear now sir

    Please advise.

    Thanks a bunch!
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Some questions!

    1) Book2 is having ID numbers but book Book1 does not. Is it possible whether the book2 can also have ID number?
    2) The status for the entries are not common in both workbooks for identifying the data and what will be the permutation combination of duplicate data. Since output data also possess duplicates and unable to use maximum time for pulling the result.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Hi Sixthsense,

    Hope all is well, and you had a good Diwali.

    1) Unfortunately, that is biggest challenge. It isn't available

    2) That is correct, they aren't same. Concatenation doesn't help too. I tried assigning IDs using Countif but that didn't help either. Again, I guess if the Combination of data from book 2 can be used to ignore the rest but pick the maximum time of the same combination, it might work.

    Please advise your thoughts...

    Thanks again!
    Last edited by [email protected]; 11-20-2012 at 01:15 AM.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Thanks for the wishes and I am fine and hope you too... Yes, the festival gone as usual and it's just a day passed in our life and I hope you enjoyed it.

    As suggested by Jeffybrown in our last post please edit your previous post just put the serial No's, 1 your reply and 2 your reply that's enough. Because repeating the same lines is just filling the screen area and it's something seems to be long discussion so that others may not interested to pitch in and share their valuable thoughts.

    Please give me sometime surely I will get back with a solution.

  5. #5
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    I'm good as well, thanks!

    Yes, enjoyed the festival as usual:-)

    And sorry, my bad. I'll surely edit it as soon as I get to my system (using my phone now). I'll also keep it in mind in all future posts.

    Thanks a bunch for taking out time to assist me. I'll lookout for your expertise.

    Best regards,
    Jai

  6. #6
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dearest Sixthsense,

    I've edited my previous post. Hope it is fine now.

    Do let me know if anything else needs to be done.

    Thanks!

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Thank you and I am working with your files only and one confirmation is required.

    Is it ok if the times are arrived from Newest to Oldest instead of Oldest to Newest?

    My output will be
    13:53:46
    11:54:54

    Instead of
    11:54:54
    13:53:46

  8. #8
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Thanks a bunch!

    My thought is that as long as the output corresponds to the exact same row, it won't really matter if the time is in asscending or descending order.

    Just one point, I need to perform this exercise on a different set of data each day, the data range could be anything between 5000-10000 or even more...

    Thanks!

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    In your attachments the Book1 is your Base data and Book2 is your Output. Now I just merged both workbook sheets in a single workbook.

    Named Book1 sheet as Base and Book2 sheet as Output.

    In G2 cell of Ouput Sheet
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    H2 cell of Output Sheet - Helper column
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    =IF(COUNTIF(Base!$H$2:$H$26,$B2&$C2&$D2&$E2),$B2&$C2&$D2&$E2,B2&$C2&$D2)

    In G2 cell of Base Sheet - Helper column
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In H2 cell of Base Sheet - Helper column
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag all the above formula's down.

    Refer the attached file for details.

    Hope this helps!
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Super Sir,

    I'm working on this and will let you know if there are any questions.

    Thanks a ton for all the help! Its going to be a "Master Piece" for sure

    Thanks a ton

  11. #11
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Need a quick advise...

    The file is crashing frequently, it starts processing till about 25% and then says not responding.

    I followed your sugesstion and tried saving it as xlsb but to no avail. It kept on processing and the crashed after 45 mins or so.

    It is basically bcoz of too much data about 5000 rows.

    Is there another trick that can be incorporated?

    Please suggest...

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Regret for the inconvenience. The suggested sumproduct function with huge data is the root cause of this problem. Please wait tomorrow i will give macro solution for this.

    Sent from mobile device

  13. #13
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    No problem at all. In fact I should be the one saying sorry!

    Just one thing, I'm not experienced in using macros, but a keen learner and think your solution could initiate the process. If you think its okay would it be possible to also give me some step-by-step instructions for running it when you share that with me. Alternatively, a different formula, as you may deem fit.

    Just wanted to be honest before you put in all the effort.

    Really value your efforts and inputs.

    I don't have words to thank you Sixthsense You are the best!
    Last edited by [email protected]; 11-20-2012 at 02:29 PM.

  14. #14
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dearest Sixthsense,

    One additional point my original data has several other columns and the placement of some of the columns shown in the sample may be different but, the placement in the original file will always remain consistent. Thought it best to let you know...

    Do let me know if you have any questions.

    Thanks!

  15. #15
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Final try with worksheet functions before going to VBA solution.

    I just added some couple of helper columns to make the formula run faster than earlier suggestion.

    Please find herewith the revised attachment.

    Some Suggestions:-
    1. While working with Huge Data File don't keep any other workbook in Open.
    2. Whenever pasting the huge data in worksheet (When Formula's are already Included) set the calculation mode of the workbook to MANUAL. After pasting the data Press F9 to perform calculation process.
    3. Another Permutation When Huge data is already there and you are just going to apply lot of formula's in cell and going to to drag it down, then in that case also set the calculation mode of the workbook to MANUAL. After applying the formula in all the desired cell Press F9 to perform calculation process.
    4. When working with huge data file with heavy calculation formula's it's better to set th calculation mode of the workbook to MANUAL and perform the calculation process by pressing F9. Else whenever any change occurs then the dependent formula's will keep on iterating and which will slow down the performance of the workbook.
    Please let us know if the performance of the revised solution, if it does not works then we go for some other alternatives.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dearest Sixthsense,

    Thank you so much for this herculean effort!

    I'm testing it now and will keep you updated on the progress.

    You are a rock star

  17. #17
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dear Sixthsense,

    Your solution is awesome! It is working beautifully…

    I incorporated your suggestions and it seems to be processing faster than earlier.

    However, I confronted with a challenge while testing my data. Request you to please review the attached file and advise….

    I’ve presented the slight modification required by way of example in the Output tab. The additional condition is based on Column B in both (Output and Base).

    Sorry about this but it is extremely crucial for it to work absolutely correctly.

    Thank you for being so supportive and patient...

  18. #18
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dear Sixthsense,

    Just wondering if you got a chance to review my request.

    My guess is that it will require a further concatenation, but didn't want to screw your awesome work so thought it best to take your advice.

    No hurry as such. Please take your time.

    Thanks

  19. #19
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Thanks for the feedback.

    You are absolutely right! I have Just added B column Data in concatenation cells.

    Refer the attached file for revision in formula's.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Superlike Sixthsense,

    I was eagerly waiting. I'll now perform my second level QC.

    Hoping all will go smoothly...

    And assuming I can comeback if there are problems:-)

    As always, you rock! Thank you:-)

  21. #21
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Hey Sixthsense,

    This is working superb. You've made my life a lot easy, and thanks for all the suggestions. The file is processing faster than before.

    I'm still testing my data, and should take another few days. I'll keep you posted on the progress.

    I very much hope that I get to work with you sometime. I'm sure it will be a great learning

    Have a great weekend!

    Cheers

  22. #22
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dear Sixthsense,

    Just a quick question...

    Is it possible to combine all helper columns, and use a single formula per tab?

    Please advise your thoughts...

    Thank you!

  23. #23
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Hi Sixthsense,

    Hope you had a great weekend!

    Please ignore my last request regarding combining.

    I'll happily work with what is available.

    Thank you!

  24. #24
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dearest Sixthsense,

    A very very big thank you for helping me solve the monster. It is working smoothly most of the times except in conditions were the data is considerably huge. In any scenario, you've made my life easy.

    You are a real genius, and I wholehartedly would like to convey "A BIG THANKS". You ROCK!!!

    Marking it as "SOLVED"

    Best regards,
    J

  25. #25
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Thanks for the feedback.

    You are absolutely right! I have Just added B column Data in concatenation cells.

    Dearest Sixthsense,

    My testing has passed with flying colours. The entire credit goes to you for making it happen:-)

    Wish I had expertise as great as you. But to be honest I'm trying, and hopefully will get better.

    I'm unsure even if I should ask this or not. Please feel free to deny or help as you may deem right!

    Based on the data this sometimes is creating a problem due to the sheet being immensely heavy. My process is also a 2 way step it is a combination of this as well as the solution you helped me with in my earlier thread "Array Formula - To determine exact matches". I'm just thinking will VBA be a better solution?

    Please advice. I'll be happy to start a new thread based on your inputs. Do let me know if you think my request isn't reasonable.

    Have a great weekend! Look forward to your inputs...
    Last edited by [email protected]; 12-03-2012 at 06:45 AM.

  26. #26
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Populate time based on condition from Book 1 to Book 2....

    Regret for the delayed reply

    I will provide you UDF (User Defined Function) solution shortly... Please don't start a new thread since it involves all the above discussions. At the same time one request from my end please let me know that the Post #19 is the last addition of conditions which needs to be considered while creating UDF solution.

    Because if you ask me to add some more conditions after the release of UDF then it will be a rework for me.

  27. #27
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dear Sixthsense,

    That is correct all the conditions in the last phase of this and the previous request stay intact there are no changes.

    Thanks,
    Jai
    Attached Files Attached Files
    Last edited by [email protected]; 12-03-2012 at 11:21 AM.

  28. #28
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    Dear Sixthsense,

    Just wanted to confirm if you were able to devote some time on this request.

    Please advise.

    Thanks you!

  29. #29
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    I'm still hopeful. Please reply. Thank you!

  30. #30
    Forum Contributor
    Join Date
    09-09-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    162

    Re: Populate time based on condition from Book 1 to Book 2....

    No response......
    Last edited by [email protected]; 12-12-2012 at 02:39 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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