+ Reply to Thread
Results 1 to 11 of 11

VBA lookup and concatenate findings

  1. #1
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    VBA lookup and concatenate findings

    Hey everybody,

    I’ve got a workbook in the attachment containing 2 worksheets, “output” and “input”.

    Worksheet “Output” contains concatenations of codes (i.e.: F090+M014+427) for which I need their descriptions from worksheet “Input”. The descriptions however are available for individual codes (i.e.: the description for “F090” is “Towels / Hips”)

    Therefore what the code should do is the following:
    Separate in background the concatenations of codes from column “Codes” in worksheet “Output” and look up and pick the corresponding code description from column “Description” – worksheet “Input”. The results corresponding to each code should be concatenated (separation symbol “/”) in column “Concatenation”- worksheet “Output”.

    My attempts till now have been unsuccessful; therefore your support is highly appreciated!

    Thanks a lot!
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    vio.coman,

    When I look at Output, some of your current code concatenations have + as you mentioned, but also brackets and slashes....
    One way to deal with is for the code to remove brackets and convert a "/" to a "+" before splitting up the codes... would that seem like it meets your criteria?

    EDIT: Also how do you want to handle a code that is not found in Input?
    Last edited by Arkadi; 06-23-2015 at 11:29 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160
    Hey Arkadi,

    Thank you for your reply!
    Yes, the current concatenations contain also brackets and slashes.
    The code should ignore all those symbols and pick only the codes from the concatenation sequences.

    What I meant with "/" is that the findings in the new concatenations should be separated by slashes.

    It would be a real help if you could give me a hand with the code.

    Many thanks!

    Quote Originally Posted by Arkadi View Post
    vio.coman,

    When I look at Output, some of your current code concatenations have + as you mentioned, but also brackets and slashes....
    One way to deal with is for the code to remove brackets and convert a "/" to a "+" before splitting up the codes... would that seem like it meets your criteria?

    EDIT: Also how do you want to handle a code that is not found in Input?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    So if two codes are separated by a "/" in the Output sheet like in Row 7 where the concatenated codes say: "F166+FW+M014+427+B03+U41+926+(701/708/709/712)"
    you want to ignore the "(" and ")" but you do want to look up 701 708 709 712 correct?

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    Try this out and let me know... as you know some of your descriptions in "Input" also contain slashes already, so it is a bit hard to follow, but seems to work:
    Rows 149, 151, and 152 cause some trouble, your formula is returning "R F222" as part of the value, which is not separated by anything that I can use to split it in 2 codes. Truth is the data returned by the formula is pretty messy, I had a few spaces and line feeds that had to be filtered out too, but best I could do.
    Please Login or Register  to view this content.
    Last edited by Arkadi; 06-23-2015 at 12:04 PM.

  6. #6
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    Re: VBA lookup and concatenate findings

    Arkadi,

    the code works like magic! thanks so much
    You're right R F222 is messy, in fact it does not exist in the sheet Input in that form.
    Could you pls do it like that, whenever R F222 appears in the current concatenation - the code should look up and return the description for "F222"?

    thanks lot once again!

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    But not for "R"?
    Because R is in the list as well I believe...

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    Ok this version drops "R " (with space as in example) from the start of any code:

    Please Login or Register  to view this content.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    If that last post gives you what you want after testing, please remember to mark the thread as solved

  10. #10
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    Re: VBA lookup and concatenate findings

    Arkadi,

    it works perfectly!

    Thanks a lot!!!

  11. #11
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA lookup and concatenate findings

    My pleasure vio, thanks for the rep!

+ 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. Findings lowest values
    By sasha162 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-16-2015, 10:29 AM
  2. [SOLVED] Counting Findings by Month
    By fjdesjardins in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2014, 12:05 PM
  3. findings cell value
    By Liju144 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2013, 01:31 PM
  4. MATCH, LOOKUP on other sheet, SUM findings
    By CHRISOK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-28-2013, 10:15 AM
  5. findings text in columns
    By tomro1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-10-2006, 08:15 AM

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