+ Reply to Thread
Results 1 to 16 of 16

Replace certain values with certain values.

  1. #1
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Replace certain values with certain values.

    I have a list of products that are extracted from our order system. These products have a category name in the order system which is located in K2. However in our planning sheet we are using less categories than there are and are grouping certain "sub categories" and I wanted to automatically change the values to the correct value. The relation between values is as following:

    On my "Lookups" sheet I have a table where VALUE in column 1 needs to be converted to VALUE in column 2 on the main sheet. I have a list of products with their correct replacement.

    For instance; Scarves, belts , headwear etc need to become Accessories. Trousers need to become Pants. But the VALUES that do not need to be replaced I will let the formula pass and use the original value in K2.

    I have made a rudimental formula that works but I'm worried it might slow down the file too much. If anyone has a better option then please enlighten me. This is the formula I currently use:

    =IF(K2="Underwear","Accessories",IF(K2="Other Accessories","Accessories",
    IF(K2="Socks","Accessories",
    IF(K2="Belts","Accessories",
    IF(K2="Headwear","Accessories",
    IF(K2="Bags","Accessories",
    IF(K2="Scarves","Accessories",
    IF(K2="Footwear","Shoes",
    IF(K2="Trousers","Pants",
    IF(K2="Sweatshirt","Sweat",K2))))))))))

    So if I can replace value with set value in my lookups sheet then that would be easier. Perhaps that is a function i do not know about yet.
    Attached Files Attached Files
    Last edited by Luxata; 12-15-2020 at 01:44 PM. Reason: formatting

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Replace certain values with certain values.

    Create a lookup list somewhere in your workbook, and then use the VLOOKUP function:

    =VLOOKUP(K2,X:Y,2,0)

    where X contains the lookup values and Y contains the values to return.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Valued Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    434

    Re: Replace certain values with certain values.

    Have you tried VLOOKUP? It could lookup the value in K2 like Underwear and give you Accessories in return.

    Maybe you can attach your file so we can get a better understanding

  4. #4
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    Hi Ali thank you for this answer,

    This does work, however I have a linked formula in an adjacent column that needs to use the result of this formula. If I use your VLOOKUP then I get the right result but my "connected" formula doesn't work anymore. (This is also a Vlookup )

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace certain values with certain values.

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    Hi,
    Yeah I wont be able to post the document since its massive and has a lot of connected formulas and vlookups. I didnt mention that if i use a vlookup on this then the rest of my formulas that need the result of this formula. And they wont work anymore.

  7. #7
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    Ok Glenn i will try to post something but i cant promise anything because i am working with multiple connected formulas over 9 sheets. Both Ali and my sollution work , however with Ali's sollution my formula that uses this outcome as base information wont work.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Replace certain values with certain values.

    We don't want to see the real data, just a small sample.

    Your final statement makes no sense whatsoever.

  9. #9
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    this is =J2&AN2 Where J2 = result of K2 and AN2 is just our retail price for this product.
    Then this outcome becomes a base for a vlookup that checks my vlookup sheet for a set value.
    That set value equals a product category and price category.
    This then becomes data for a Pivot table.
    That pivot information is being pulled by a SUMIFS formula Which shows on my front sheet how many products and how much we spent on lets say accessories price group 1.

    Sorry if this is a bit confusing on how i write it down. But if I use a vlookup result to power a vlookup it will give me an NA error .
    Last edited by Luxata; 12-15-2020 at 10:52 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Replace certain values with certain values.

    You don't need to change what is in K2. I don't think you've understood the advice.

    There are instructions at the top of the page explaining how to attach your sample workbook.

  11. #11
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    I have attached the preview file. Also i have used the Vlookup from ALI on the first two lines. In the sample file the vlookups i normally have on another sheet are on the right. Hope this will clarify some stuff i was saying.

    So in this file I am trying to convert value in D2 to value specified in R2. But B2 value is very important for me as you can probably see all the linked formulas now. I have simplified it all

    Again thank you all for the patience.
    Last edited by Luxata; 12-15-2020 at 11:19 AM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace certain values with certain values.

    Right then...

    1. Accessories was incorrectly splled in R1 to R7.

    2. Formula in C2 copied down the column.

    Column H. I used CTRL H to replace all decimal commas with decimal points. You may need to reverse that to get it working in DK. So I have attached two versions of your file. Which one works (if either???)

  13. #13
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    Hi Glenn! Thank you so much!
    I guess I spelled it correctly in the IF formula, but in the reference table I did not .. doh!

    As you can see my native language is not English and something about the word accessories I just blank out. Anyways thanks a bunch for the help !
    I have updated the reference table in the original file and now its working.. Also thanks to AliGW for the first solution !

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace certain values with certain values.

    Well, your English is better than my Danish!! Which file worked for you, by the way?

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  15. #15
    Registered User
    Join Date
    03-09-2020
    Location
    Denmark
    MS-Off Ver
    MS Office 365
    Posts
    11

    Re: Replace certain values with certain values.

    Glenn, both are working fine, however for our financial formatting we use commas

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Replace certain values with certain values.

    OK.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 7
    Last Post: 10-21-2016, 06:16 PM
  2. [SOLVED] VBA Code replace old values to new values depends upon column values
    By johnlara in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-09-2015, 08:19 AM
  3. [SOLVED] Help needed with VBA code to replace values with names and delete others Values
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-10-2015, 01:50 AM
  4. [SOLVED] vba to Replace Old values by new values depends upon cell values in AC:AC col
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-25-2014, 08:15 AM
  5. Macro to identify column by header and replace number values with string values
    By kang6789 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2013, 05:44 PM
  6. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  7. Mutiple values to replace with multiple values random in fields
    By CatalinNic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-18-2011, 01:52 PM

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