+ Reply to Thread
Results 1 to 10 of 10

Finding and subtracting with almost similar product SKUs

  1. 09-07-2012, 11:58 AM #1
    ExcellentM is offline
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Finding and subtracting with almost similar product SKUs

    Hi all.......New to the forums....

    This task has me a bit stumped, as I'm not yet fluent in formulas. Basically, I have a large column list with product SKUs. The example shows that pairs of SKUs are similar, but one has additional characters for a special version of the product and is more expensive. I'm trying to come up with a formula that finds the higher item (500FBCLIKA-BOD), get the price, and then subtract the price of the similar, lower value SKU (500FBCLIKA).




    A B C
    1 SKU Price Difference
    2 500FBCLIKA 383
    3 500FBCLIKA-BOD 495


    All I could come up with is:

    =VLOOKUP(A2&"-BOD",$A$2:$A$1898,2,FALSE)-A2

    Which of course does not work.

    Any of you Spreadsheet Wizards know what I should be correcting or using?

    Thanks!
    Last edited by ExcellentM; 09-07-2012 at 02:03 PM.
    Reply With QuoteRegister To Reply
  2. 09-07-2012, 12:02 PM #2
    twiggywales is offline
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Finding and subtracting with almost similar product SKUs

    how would you be picking them? i assume you have the part code of the standard one? and that would be used to get the others. how many vairiations are there? just 1 per item or mutiple?
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star
    Reply With QuoteRegister To Reply
  3. 09-07-2012, 12:10 PM #3
    ExcellentM is offline
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Re: Finding and subtracting with almost similar product SKUs

    Oops.....Sorry I forgot to specify that..

    The ending -BOD is always the same in the entire column when it occurs. Sometimes the preceding part of the SKU may be two or three characters longer than others.
    Reply With QuoteRegister To Reply
  4. 09-07-2012, 12:18 PM #4
    twiggywales is offline
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Finding and subtracting with almost similar product SKUs

    can you have a helper column
    Reply With QuoteRegister To Reply
  5. 09-07-2012, 12:22 PM #5
    ExcellentM is offline
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Re: Finding and subtracting with almost similar product SKUs

    Sure.......

    Just tried this too which didn't work:

    =VLOOKUP(SUBSTITUTE(A2,A2,CONCATENATE(A2&"-LED")),$A$2:$A$1898,2,FALSE)-A2

    Am I close?
    Reply With QuoteRegister To Reply
  6. 09-07-2012, 12:37 PM #6
    twiggywales is offline
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Finding and subtracting with almost similar product SKUs

    see the attached book


    sku lookup.xlsx

    that should give you something to work at.

    unfortunatly i am about to go out so wont be here to help any further till tonight if it doesnt help ill try later but in the mean time someone else may be able to assist
    Reply With QuoteRegister To Reply
  7. 09-07-2012, 01:23 PM #7
    ExcellentM is offline
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Re: Finding and subtracting with almost similar product SKUs

    Well...The first part worked to fill Column C with the same prices. I had to change the reference to SEARCH(E2,A2,1)....Not $E$2.

    The second part doesn't work at all. Is it because my Column A has about 1,000 of these SKU pairs? I don't get how MAX and MIN in the equation relate to the entire columns.
    Reply With QuoteRegister To Reply
  8. 09-07-2012, 01:46 PM #8
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: Finding and subtracting with almost similar product SKUs

    The only thing that is wrong in your original formula is the lookup range
    Change it to 
    Please Login or Register  to view this content.
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  9. 09-07-2012, 01:47 PM #9
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: Finding and subtracting with almost similar product SKUs

    The only thing that is wrong in your original formula is the lookup range
    Change it to 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  10. 09-07-2012, 02:01 PM #10
    ExcellentM is offline
    Registered User
    Join Date
    09-07-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010 Mac
    Posts
    29

    Re: Finding and subtracting with almost similar product SKUs

    Finally...GOT IT!

    =VLOOKUP(CONCATENATE(A2&"-BOD"),$A$2:$B$1898,2,FALSE)-B2
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1