+ Reply to Thread
Results 1 to 36 of 36

VBA Required to compare 4 set of datas & pick the required data

  1. #1
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    VBA Required to compare 4 set of datas & pick the required data

    hi Friends,
    i have a repeated kind of work doing in a excel file, where i will explain the manual steps below,
    actually i have 4 set of datas(highlighted in green) in column wise & each contains the subtotal(Highlighted in Red)
    at the right corner of each set of datas,based on Availability of this Subtotal, we should copy required datas.

    could someone please help me to provide the VBA to do the same, it will be a great help to me & reduce my workload

    STEPS:
    1.Open the workbook & goto cell range AC2, Here the values in cell range(AC2, W2, O2, H2) are the subtotals
    2.Goto AC2 & check for numerical values, IF Available then i m pressing CTRL+DOWN, then SHIFT+1 step UP then SHIFT+3 cells LEFT
    so here my required data is selected & now it can be pasted in AE2.
    3.IF AC2 subtotal field is Blank / #NAME?, then it should skip that set of data & check for next set of data.

    the same above process has to be done till the end of sheet.
    for better understanding i have attached the sample sheet & highlighted the output values in Blue at the end.

    kindly request some expert to reduce my manual workloads.
    Thanks in Advance.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    could someone please help me on this thread. i m sorry for following the above thread in next few hours,
    please let me know if the above explanation is not understandable.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,441

    Re: VBA Required to compare 4 set of datas & pick the required data

    Administrative Note:

    We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been only 9 hours since you posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    520

    Re: VBA Required to compare 4 set of datas & pick the required data

    Assume your request counting headers all have "sub total"
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Thank you so much for looking on this Post Mr.ikboy,

    Actually i got result as 0 in all the fields(AH column) after running the code,
    may be i m wrong somewhere ...could you please check the attached screenshot.
    Attached Images Attached Images

  6. #6
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    i m sorry Mr.alamsidman, i understand the your explanation, hereafter i wont happen

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Add some more required data in blue area as this is not giving clear requirement.
    You start with AC2 then what is the next cell in your data, it is not clear.
    First H2 data selected.Then W11 data taken . What is the logic.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    520

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by amb2301 View Post
    Thank you so much for looking on this Post Mr.ikboy,

    Actually i got result as 0 in all the fields(AH column) after running the code,
    may be i m wrong somewhere ...could you please check the attached screenshot.
    green circle no data
    Attachment 698753
    Attached Images Attached Images

  9. #9
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    i m sorry for making confusion here Mr.ikboy, i will try to explain the logic once again here,

    actually i need to compare the 4 set of datas available (green highlighted datas in sample file) from Right to Left
    i need to check the Red higlighted values on each set of Green highlighted datas starts from AC2, if it contains any datas other than 0,
    then it copy that set of Datas & get pasted onto Blue higlighted areas(AE to AH column).
    in earlier sample datas in blue cells are just for understanding manually i copied & paste it.....it will blank always...

    i just tried recording a macro ....if my explanation is poor, please refer the below recorded macro....

    for example: if W2 is also 0 , then it should check O2 , if O2 contains values, then it should copy the set of datas & pasted in blue cells(AE to AH column).

    HTML Code: 
    Sub Macro6()
    
    If Range("AC2") = "0" Then
     Range("W2").Select
     Else
        Range("AC2").Select
        Range(Selection, Selection.End(xlDown)).Select
        ActiveCell.Offset(0, -3).Range("A1:D9").Select
        ActiveCell.Activate
        Selection.Copy
        ActiveWindow.SmallScroll ToRight:=8
        ActiveCell.Offset(0, 5).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, -5).Range("A1").Select
        Application.CutCopyMode = False
        End If
    End Sub
    Last edited by amb2301; 10-08-2020 at 09:28 AM.

  10. #10
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Add some more required data in blue area as this is not giving clear requirement.
    You start with AC2 then what is the next cell in your data, it is not clear.
    First H2 data selected.Then W11 data taken . What is the logic.
    Sir, i tried to explain the logic in thread#9

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by amb2301 View Post
    i need to check the Red higlighted values on each set of Green highlighted datas starts from AC2, if it contains any datas other than 0,
    then it copy that set of Datas & get pasted onto Blue higlighted areas(AE to AH column).
    [/HTML]
    Then AH2 should be 150739 ie AC2 value but it is 150327. How it is.
    One more clarification.Do you need VBA code only or Formula will do.
    Last edited by kvsrinivasamurthy; 10-08-2020 at 10:19 AM.

  12. #12
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Then AH2 should be 150739 ie AC2 value but it is 150327. How it is.
    i m extremely sorry sir, in the sample file just i made some manual entries, might gone gone wrong at that time,
    uploading the correct sample....Thanks for finding my mistake sir ....AH2 should be 150739.
    Last edited by amb2301; 10-09-2020 at 12:55 PM.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    One more clarification.Do you need VBA code only or Formula will do.

  14. #14
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    if its VBA .....It would be a great help for me Sir.....Sorry if i am troubling you Sir

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    No trouble. I thought that Formula Will give live data. VBA you have to run every time. Shall I go ahead with VBA.

  16. #16
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    No trouble. I thought that Formula Will give live data. VBA you have to run every time. Shall I go ahead with VBA.
    please provide VBA, actually i have macro, which is used to combine these datas from different sheets, i will just call your Macro at the end of it Sir.

  17. #17
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    fyi.....after the end of this work, i will just keep following datas (A to D column & AE to AH column), rest datas in between will be deleted Sir.

  18. #18
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Here is the code.Each block is taken as 9 rows in code. But some appear has only 4 rows.Pl clarify.
    Eg:Row 38 and Row 42

    Please Login or Register  to view this content.
    NOTE: Some cells have Error. It is better to avoid such things.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-08-2020 at 12:21 PM.

  19. #19
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Here is the code.Each block is taken as 9 rows in code. But some appear has only 4 rows.Pl clarify.
    Eg:Row 38 and Row 42

    Please Login or Register  to view this content.
    NOTE: Some cells have Error. It is better to avoid such things.


    i have no other words to say....just Awesome!!!! Sir.....Thanks,and Thanks and Ever Thanks.
    it worked perfectly

  20. #20
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Here is the code.Each block is taken as 9 rows in code. But some appear has only 4 rows.Pl clarify.
    Eg:Row 38 and Row 42

    NOTE: Some cells have Error. It is better to avoid such things.


    Hi Sir,
    Actually the 9 step is not a constant, it varies....sometimes it may be 4 rows, it may be 3 rows, .....so based on it , code should move to the next block selection,
    i Checked with my Master sheet, then got the problem & realized your above mentioned comments.

    Sir,Please let me know if my explanation is poor.
    & Sorry for not testing it properly earlier itself.

    Thanks in Advance.
    Last edited by amb2301; 10-08-2020 at 07:34 PM.

  21. #21
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Here is the required code.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Thank you Sir, i will check now & get back to you Sir

  23. #23
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Here is the required code.
    Please Login or Register  to view this content.


    Awesome Sir,
    This is the Exact thing i required Sir, i check with master sheet also, it worked perfectly.
    Thank you so much for your Help Sir & Great work as usual Sir

  24. #24
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Thanks for feed back and comments.

  25. #25
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Sir,
    Provided Macro works fine for almost all the files,
    only in few files getting struck, Actually the issue is...
    when the condtion comes like....All the 4 subtotal values are 0, then the code get stopped over there,
    so if this kind of condition comes,it should get skipped to next set of data & proceed the code.

    i have a attached a sample file & highlighted the cells in Yellow, which is having issues.
    Sir, could you please check it.

    Thanks in Advance.
    Attached Files Attached Files

  26. #26
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    All subtotals are 0in Yellow cells. What is the expected result. Pl upload file with expected result.
    Last edited by kvsrinivasamurthy; 10-11-2020 at 02:20 AM.

  27. #27
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Sir,
    Updated the Expected Thing in attached sheet.Please have look on it Sir.
    Attached Files Attached Files

  28. #28
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Here is the modified code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-11-2020 at 02:50 AM.

  29. #29
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Sir,
    Thatz too fast reply.....Awesome....it worked like a charm
    i heartily appreciate your efforts Sir.
    Thank you so much.

    i m going to run with another set of 10000 set of Data, hoping no issues will come(Pray for me Sir)

  30. #30
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Just I have changed the code to make it faster. Once again you take the code edited post #28.

  31. #31
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Quote Originally Posted by kvsrinivasamurthy View Post
    Just I have changed the code to make it faster. Once again you take the code edited post #28.
    Thank you Sir, i will take updated code.

  32. #32
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Pl run the code on a copy original file.

  33. #33
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Yes, i m doing it sir

  34. #34
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Modified code
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    12-12-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    179

    Re: VBA Required to compare 4 set of datas & pick the required data

    Thanks for the Modified Code, it worked even faster

  36. #36
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,779

    Re: VBA Required to compare 4 set of datas & pick the required data

    Welcome. Thanks for feedback.

+ 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. [SOLVED] VBA required to paste datas in to visible cells
    By amb2301 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-07-2020, 01:35 AM
  2. Formula Required to add on Numbers as required to other data
    By choccy_77 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-01-2020, 01:16 PM
  3. Replies: 4
    Last Post: 09-20-2013, 07:03 AM
  4. Help required in matching data in required format
    By pravindwd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2013, 09:03 PM
  5. Help required in matching data in required format
    By pravindwd in forum Access Tables & Databases
    Replies: 1
    Last Post: 05-04-2013, 04:11 PM
  6. [SOLVED] Required the desired result from some datas
    By PRADEEPB270 in forum Excel General
    Replies: 2
    Last Post: 04-29-2010, 07:24 AM

Tags for this Thread

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