+ Reply to Thread
Results 1 to 18 of 18

Usability of SKU'S Check

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Red face Usability of SKU'S Check

    Hi All,

    Hope you are doing well!...I have a list of items and the number of available SKU'S for that item ...If the number is 0 or negative then I need to output as No and if the number is positive then I need to output as Yes...

    My status are in the formats of Available (46),Available (-4),Disabled (-628) for which the outputs should be in the format of Yes,No,No respectively...Can you please help here...Please find attached the sheet with the expected output...

    Also if there are just numbers in the status column like 42232,43304,44321 the output should be No

    Thanks,
    Arun
    Attached Files Attached Files
    Last edited by chandramouliarun; 06-10-2020 at 12:15 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    One way:

    =IF(VALUE(SUBSTITUTE(MID(Table1[@status],FIND("(",Table1[@status])+1,9),")",""))>0,"Yes","No")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Try this...
    =IF(ISERROR(FIND("(-",B2)),"Yes","No")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    I thought so initially, but it returns the wrong answer for 0, Ford.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Ali, that gives an error on the last 5 entries because there is no (...thats why I went the other way, find (-

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Quote Originally Posted by AliGW View Post
    I thought so initially, but it returns the wrong answer for 0, Ford.
    HA we both got some part wrong

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    OK, but yours is wrong for 0.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    There are no entries without brackets, though ...

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    Oh, thatís naughty! The OP has changed the sample sheet without telling us.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    OK so it got a bit longer...
    =IF(ISERROR(--MID(B2,FIND("(",B2),99)),"No",IF(--MID(B2,FIND("(",B2),99)=0,"No","Yes"))

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Quote Originally Posted by AliGW View Post
    Oh, thatís naughty! The OP has changed the sample sheet without telling us.
    bwahahahaha, that was really funny!!

  12. #12
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Usability of SKU'S Check

    Hi AliGW,

    Sorry, I just realized the mistake and updated the sheet...Thanks for help !It works for me..Anyway I need to exclude the numbers ..

    Thanks,
    Arun

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    Try this:

    =IF(ISNUMBER(Table1[@status]),"No",IF(VALUE(SUBSTITUTE(MID(Table1[@status],FIND("(",Table1[@status])+1,9),")",""))>0,"Yes","No"))
    Last edited by AliGW; 06-10-2020 at 12:39 AM.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    (cant edit my prev post) but hey, at least they have included some realistic data for us

  15. #15
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Usability of SKU'S Check

    Thanks AliGW and FDibbins!!!..Your help is much appreciated and your solution works!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,482

    Re: Usability of SKU'S Check

    No problem.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Happy to help

    (still trying to shorten that thing lol)

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,106

    Re: Usability of SKU'S Check

    Happy to help and thanks for the feedback

+ 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. How to check if excel file is Checked Out in SharePoing Check Out, run code, Check In
    By jrtraylor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2016, 04:36 PM
  2. Automatically check one or more check boxes when a parent check box is manually checked
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2013, 09:43 PM
  3. Excel for Mac 2011 3 problems re tickboxes, iPad usability and file sizes
    By Algernon in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 03-16-2013, 12:29 AM
  4. Replies: 1
    Last Post: 12-14-2012, 10:11 AM
  5. UDfs to react similarly to built-in functions pertaining to spreadsheet usability
    By ExcelSausage in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-10-2012, 05:15 PM
  6. Improving usability of addins in 2007 and 2010
    By Cheeky Charlie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-30-2012, 08:11 AM
  7. Excel Usability
    By jwcane in forum Excel General
    Replies: 0
    Last Post: 01-01-2012, 10:22 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