+ 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
    Office 365
    Posts
    220

    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
    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: 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!
    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
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    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: 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    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: 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
    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: 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
    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: 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    Office 365
    Posts
    220

    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
    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: 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    Office 365
    Posts
    220

    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
    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: 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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: 0
    Last Post: 03-28-2013, 01:56 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