+ Reply to Thread
Results 1 to 14 of 14

How to lookup values between a delimiter and replace based on range?

  1. #1
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    How to lookup values between a delimiter and replace based on range?

    Greetings!

    I need some help with VBA or a formula to do the following:

    Column A contains Part Numbers. Column B contains that part number's database ID. Column C (RelatedProducts) contains a string of ID's that are related to this part number. If there is more than one related product, the values are separated by a semi-colon as the delimiter.

    I'm trying to substitute each ID in Column C (RelatedProducts) with the corresponding part number, using columns A & B as the lookup table. Again, Column C may contain multiple related ID's, so the function needs to find the corresponding part number for each value between the semi-colons, copy the part numbers to Column D (but now separated by a comma instead of a semi-colon).

    I'm not very skilled in VBA, so this is way beyond me. I've included a spreadsheet that contains a sample of the data, plus Column D (Output) that shows what the result should look like. I'd be so grateful for any help.
    Attached Files Attached Files
    Last edited by cda; 10-25-2010 at 12:05 PM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    Try this:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    StephenR, thank you so much. It's perfect except for one issue - if a product does not have any related products in Column C, the code dies. Is there any way to name the range (the master sheet I have contains 2400 rows) and force it to continue to row 2401 and ignore blanks until then?

    In the meantime, I'm going to study your solution and try to determine how it works - this is a great learning experience.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    I think if you just change the For line it should overcome that problem
    Please Login or Register  to view this content.
    You could name the range but I haven't.

  5. #5
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    Well, I solved the blanks problem by just inputting a dummy part number and ID at the end of the data and doing a find/replace for blanks and putting the dummy info in. This way i can just filter and remove.

    Thank you!

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    My pleasure. My amendment above should obviate the need for you to input any dummy data. I had just assumed (wrongly) that there were no blanks.

  7. #7
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    Thanks for the change StephenR. The other issue I'm struggling with is if an ID doesn't exist. For example, PartNumber 8702 has related product ID's of 39,40,49. ProductID 49 doesn't exist, and when the code runs it doesn't return anything in Column D. If I had a line and put 49 in the productID column, and "DUMMY" in the SKU, it brings "DUMMY" over with the values for 39 & 40.

    How would I change the code to ignore ID's that don't exist and just go on to the next one.

    Thank you so much.

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    Sorry, I don't follow. At the moment the code goes down column C, for each entry looks it up in column B and returns corresponding entry in A. If it's not found in B it doesn't return anything. What am I missing?

  9. #9
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    I apologize for not explaining better. I hope this makes more sense.

    I thought that the values were not being written in Column C if an ID from Column B was referenced and didn't exist.

    What I didn't realize until I physically clicked in one of the cells is that it's writing the values, but for some reason the values in the cell are not visible unless I click on it; then, the value is only visible in the formula bar. I've tried changing the formatting of the affected cells and I still can't see them, however if I copy the cell to another worksheet, it becomes visible. Strange, but it can be overcome by copy/paste.

    What I can't seem to overcome is if one of the id's doesn't exist, the function isn't inserting a comma between the values that do exist. I've uploaded a range of cells that I copied from the master sheet. On row 33 you can see that Part Numbers 8703 & 8704 were copied, but no comma was inserted between them.

    Looking at the code I can see that you're splitting the values between the semi-colons, doing the lookup, then on line 9 inserting a comma between the returned values. For some reason this isn't working when one of the id's doesn't exist. I'm not sure where to begin to fix that.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    I couldn't replicate your problem, but if you add this line just before End Sub does it then work?
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    Unfortnately it didn't. The comma is still not inserting between the PartNumbers when one of the referenced ID's doesn't exist.

    I've tried changing the number format (number, text, general), however it appears this only happens when the related PartNumbers are number strings - it doesn't happen if there is an alpha character or non-numeric string.
    Last edited by cda; 10-25-2010 at 10:37 AM.

  12. #12
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: How to lookup values between a delimiter and replace based on range?

    I can't replicate your problem. Can you post a problem example? I just noticed you are using Excel 2010. I don't have this and while I have no reason to think it should make any difference I suppose there is a chance it might. Do you have access to any other versions?

    EDIT: I was able to replicate your problem, but oddly when I ran the code again it worked. You might try adding that line at the beginning before the For line
    Please Login or Register  to view this content.
    Last edited by StephenR; 10-25-2010 at 11:59 AM.

  13. #13
    Registered User
    Join Date
    10-22-2010
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: How to lookup values between a delimiter and replace based on range?

    That did it! Moving the last piece of code before the "For" statement seemed to make the difference.

    Thank you SO much!

  14. #14
    Registered User
    Join Date
    03-06-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: How to lookup values between a delimiter and replace based on range?

    I am trying to substitute a range of text separated by commas in a cell into corresponding values separated by commas and then find the sum of those numbers. For example, I have A,B,C,D in one cell. I want to have a cell with the corresponding values(say) 3,4,5,6 and in the next cell, I need the sum of 3,4,5, and 6. Could somebody please help me?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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