Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts

    Extract data to seperate sheet where reference matches

    I have source data (codes.xlsx) and destination data (Inventory (amended).xlsx)


    I need a VBA code to extract the codes for each reference in Col b onwards , that matches the reference in Col A in the source data but not to extract the codes LK-65915 & LK-65910. I would like these extracted on a separate sheet called "Final Result"



    Your assistance in assisting me is most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    It is easy to recreate what you want with vba, but it would be better if you could post another additional example, just so I understand the columns you are using i.e. will it always be column [G] in the codes.xlsx file????

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Zeddy

    The codes will always be in Col G


    Howard

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    In your posted example Inventory file, you have duplicate values in col [A] for your Ref values.
    In your posted example Codes file for your extract Codes, you also have duplicate values in the 'lookup' col [A] and the 'return Code' can be different for the same Reference.

    So do you want us to return the 'first-matching-Code' for the 'first-matching-Reference", then the 'second-matching-Code' for the 'second-matching-Reference', the 'third-matching-code' ... etc etc etc????
    If this is the case, what do you want us to do if there are, say, 4-identical-References in the Inventory file but only '2-matching-References' in the Codes file???

    ..or, if life is really simple, are the Codes file entries in column [A] always perfectly aligned and matching with the Ref entries in col [A] of the Inventory file????

    (I have a file ready to post back, but no point if it doesn't do exactly what you want)

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Zeddy

    It is more complicated than what I originally anticipated

    I want to return the 'first-matching-Code' for the 'first-matching-Reference", then the 'second-matching-Code' for the 'second-matching-Reference', the 'third-matching-code' ... etc etc et

    However, if for eg there are 4-identical-References in the Inventory file but only '2-matching-References' in the Codes file, then match up the number of references in the codes file. There should be the same number of references in both files, where the references match. The files uploaded were just sample files

    Howard

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    OK, that makes it easier to follow.
    I'll update my file and post it after testing.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    There should be the same number of references in both files, where the references match.
    OK, so here's my file that should do what you ask.

    We could amend the code to put a "??" or a "-" if there were say, 5 similar-Ref-items in the Inventory file and only say, 2, corresponding items in the Codes file i.e. for the last 3 of the similar-Ref-items we would put a "??" in the returned code to show that they weren't found in the external file. But if it's not needed the attached file should do.

    zeddy
    Attached Files Attached Files

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    Just a note - the method I used in my posted file would work regardless of any order of the values in the external Codes file.

    And, of course, if there were no matching item for a particular column A Ref value in the Inventory file, it will return #N/A to indicated this.

    zeddy
    Last edited by zeddy; 2018-11-09 at 05:33.

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Zeddy

    Thanks for all the effort. Your code works perfectly. Will be testing the live data next week and will advise if I need any further help where I pick up any problems that I cannot resolve myself


    Howard

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    I shall be in St Lucia briefly next week, and then enjoying a break in Barbados for just three weeks.
    Back in the UK early December.

    Good luck with the live data test.

    zeddy

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Zeddy

    Enjoy your break. A 3 week break will be great way to unwind in Barbados. Never been there , but believe it is very nice


    Howard

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Hi Zeddy

    I have similar spreadsheets to post #1 , which I have attached but am battling to amend the code to copy and paste the final result

    The spec is the same as in my earlier post except that the source data reference is called "Posting Reference (1)" is in Col N and on sheet2



    It would be appreciated if you could amend


    I have source workbook (Raw Data import) where I need match Reference in Col A (sheet1) to Posting Reference (1) on sheet2 in the destination file

    I want to return the 'first-matching-Code' for the 'first-matching-Reference", then the 'second-matching-Code' for the 'second-matching-Reference', the 'third-matching-code' ... etc etc et

    However, if for eg there are 4-identical-References in the Inventory file but only '2-matching-References' in the Codes file, then match up the number of references in the codes file


    Where there is a match then the reference number and account number (Col G ) to be extracted in sheet Final result


    Kindly amend your code regarding the above


    Howard
    Attached Files Attached Files

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,351
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi Howard

    It didn't need much of a code change.
    see attached file; (in the VBA, search for "v1b")
    I just changed the code from..
    Code:
    Worksheets("Final Result").Select           'switch to ouput sheet
    Cells.Clear                                 'clear everything on sheet
    Sheets(1).[A:A].Copy [a1]
    to..
    Code:
    Worksheets("Final Result").Select           'switch to ouput sheet
    Cells.Clear                                 'clear everything on sheet
    Sheets("Imported Data").[N:N].Copy [a1]     '<<v1b; copy column [N] from second sheet
    See if the attached file does what you need.

    ..back to last-minute packing for the trip tomorrow

    zeddy
    "a day without sunshine is like night"
    Attached Files Attached Files

  14. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,566
    Thanks
    145
    Thanked 7 Times in 7 Posts
    Thanks very much for the help Zeddy

    The code works perfectly. I need one slight change. Where the References match to the source data, I also need the "Goods Value" in column Q to be extracted to sheet "final Result"


    You can do this whenever you get a chance


    Hope you have a well deserved rest


    Howard
    Attached Files Attached Files

  15. #15
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    1,158
    Thanks
    101
    Thanked 136 Times in 121 Posts
    Quote Originally Posted by HowardC View Post
    You can do this whenever you get a chance
    Very thoughtful

Posting Permissions

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