Results 1 to 8 of 8

Thread: VBA: Add Data to another column in another sheet depending on a condition

  1. #1
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    26
    Articles
    0
    Excel Version
    Excel for Mac 16.4

    VBA: Add Data to another column in another sheet depending on a condition



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have 2 sheets. The first column in both sheet A and Sheet B have the same ID no in that column.

    If there is an entry greater than 0 in Sheet 1 Column B (called Data A), then copy that entry to Sheet 2 Column B (Data B) against the correct ID in that Row.

    What formulae do I need to put in Sheet 2 Column B (data B) in order to pull this data from Sheet 1 please ?

    The reason behind this is that I have many Sheet 1s that do not have rows with consecutive row number on so I am copying data by hand from the ID no on on Sheet 1 to the corresponding ID no on Sheet 2.

    So the formula needs to look at the ID no on Sheet 1 and if there is a value in that row greater than 0 (Data A) , then copy that number to the correct row on Sheet 2 - which I hope makes sense.

    Any help with this would be much appreciated. Please see Sample file attached.

    Best wishes,


    Stephen
    Attached Files Attached Files

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    Stephen,https://www.screencast.com/t/688RYPOPtgNfHere you go just do a basic merge in PQ directly or if you have multiple files in the same work book consider accessing that file using a brand new Excel workbook and selecting the root of the the populated workbook that way as new tabs are added you can automatically refresh these puppies into the build versus having hardcoded the individual sheets and having to constantly change the query for newly added tabs.Knock 'em Dead Sailor!
    Attached Files Attached Files

  3. #3

  4. #4
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    26
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Huge thanks Ed for the reply. Just a quick final question - will this still work if the ID numbers of the sheet in the first column I want to merge does not have consecutive rows of data in so for example:

    ID. Data
    3 45
    5 67
    8 30

    Needs to merge with a sheet that has

    ID. Data
    1
    2
    3
    4
    5
    6

    I need to line up data with the correct ID number row.

    Many thanks for the help - much appreciated.

    Best wishes,


    Stephen

  5. #5
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    Yes it will stephen

  6. #6
    Acolyte sjo007's Avatar
    Join Date
    Dec 2015
    Posts
    26
    Articles
    0
    Excel Version
    Excel for Mac 16.4
    Thanks Ed - I have just realised that Mac Excel with O365 (Version 16.51) which I have does not have PQ - sadly. Is there a way to do this without PQ please ?

    Huge thanks for the help.

    Best wishes,


    Stephen

  7. #7
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    182
    Articles
    0
    Excel Version
    2016
    Consider reposting it and perhaps one of the VBA guys can quickly pull some code together

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,721
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    No need to re-post - I’ve moved this to the VBA section.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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