+ Reply to Thread
Results 1 to 5 of 5

Treat 0 and 00 as two different values

  1. #1
    Registered User
    Join Date
    07-30-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    2

    Exclamation Treat 0 and 00 as two different values

    Requirement:
    1. Cell must accept values 0,00 and 1 to 99
    2. Check for duplicates.

    I set a range for all the above mentioned values. Set the format to Text so that it accepts 00 as well.

    In the field i set data validation rule as =AND(COUNTIF($V$1:$V$101,F12), COUNTIF(F12:F23,F12)=1) to check for duplicates and value range.

    $V$1:$V$101 - contains values 0,00 and 1 to 99
    F12:F23 - set of 12 cells where i need to enter the valid values and check for duplicates.

    When i enter values in the fields. it does not allow me to enter 0 in another cell if 00 is already entered and vice versa.

    Please help me out.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Treat 0 and 00 as two different values

    Please see yellow banner of how to post a workbook.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Treat 0 and 00 as two different values

    I agree to post a workbook, as per the banner
    I am not sure what =AND(COUNTIF($V$1:$V$101,F12), COUNTIF(F12:F23,F12)=1) does but if the problem is that 00 is treated as 0
    try
    =AND(sumproduct(($V$1:$V$101=F12)*1), sumproduct((f12:F23=F12)*1))=1)

  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: Treat 0 and 00 as two different values

    Have a read of this recent thread: https://www.excelforum.com/excel-for...and-1-2-a.html

    The solution there uses the EXACT function.
    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.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Treat 0 and 00 as two different values

    @ Ali
    Thank you for that link.
    Dave

+ 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. [SOLVED] how conditional formatting treat values that is not actualy same
    By keshavtale in forum Excel General
    Replies: 6
    Last Post: 09-25-2017, 04:59 AM
  2. How to treat data in listbox as VALUES
    By Rudo123 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-26-2015, 02:23 PM
  3. Make Excel charts treat certain values as =na()
    By davidx in forum Excel General
    Replies: 5
    Last Post: 06-20-2015, 04:49 AM
  4. [SOLVED] Treat numbers above a certain value as that value when summing
    By sir stoffer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-10-2013, 04:52 AM
  5. [SOLVED] How lines in combination charts treat #N/A values
    By Bernard Harris in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-16-2006, 12:20 AM
  6. [SOLVED] How to make excel to treat values in cell as a number?
    By Peri in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2005, 06:45 PM
  7. Excel - treat pairs of figures differently according to values
    By Alistair in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2005, 06:06 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