+ Reply to Thread
Results 1 to 6 of 6

Excel wrongly treats A and A* as duplicate

  1. 02-02-2020, 03:33 AM #1
    amsin21
    amsin21 is offline
    Registered User
    Join Date
    02-02-2020
    Location
    DXB
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Question Excel wrongly treats A and A* as duplicate

    Hi,

    On conditional formatting, Excel treats A & A* as duplicates. Sample attached for reference. Kindly advise.

    Thanks & regards,
    Ajit


    Excelduplicateerror.png
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-02-2020, 04:16 AM #2
    AliGW
    AliGW is offline
    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
    80,869

    Re: Excel wrongly treats A and A* as duplicate

    Welcome to the forum.

    Excel will (CORRECTLY) see A and A* as duplicates because the asterisk "*" is a dedicated Excel wildcard character. Once I know what you are tring to do, I'll be able to tell you how to overcome this.

    EDIT: In my insider version of Excel 365, the * is not being read as a wildcard, so the conditional formatting does not flag duplicates.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    		 ITEM_CO_Code ITEM_CO_Name ITEM_CO_Unit
    2
    		 A ATEST PCS
    3
    		 A* Atest1 PCS
    Sheet: Sheet1
    Last edited by AliGW; 02-02-2020 at 04:30 AM.
    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.
    Reply With QuoteRegister To Reply
  3. 02-02-2020, 04:38 AM #3
    amsin21
    amsin21 is offline
    Registered User
    Join Date
    02-02-2020
    Location
    DXB
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Excel wrongly treats A and A* as duplicate

    Dear Ma'am,

    Accidentally I mentioned Excel version as 2016. It's MS Office 365 Business version.

    I want both entries to be treated as separate. I have attached my sample product file. Kindly advise.

    Thanks & regards,
    Ajit
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 02-02-2020, 04:45 AM #4
    AliGW
    AliGW is offline
    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
    80,869

    Re: Excel wrongly treats A and A* as duplicate

    Try changing the CF rule to this formula rule:

    =AND(A1<>"",COUNTIF($A:$A,SUBSTITUTE(A1,"*","~*"))>1)

    PS Please update your profile to Office 365 Business. Thanks.
    Attached Files Attached Files
    Last edited by AliGW; 02-02-2020 at 04:52 AM.
    Reply With QuoteRegister To Reply
  5. 02-02-2020, 04:51 AM #5
    amsin21
    amsin21 is offline
    Registered User
    Join Date
    02-02-2020
    Location
    DXB
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Excel wrongly treats A and A* as duplicate

    Perfect... done the job...

    Thanks & regards,
    Ajit
    Reply With QuoteRegister To Reply
  6. 02-02-2020, 04:53 AM #6
    AliGW
    AliGW is offline
    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
    80,869

    Re: Excel wrongly treats A and A* as duplicate

    Great - please update your user profile to Office 365 Business. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    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)

Similar Threads

  1. [SOLVED] Excel treats empty cell as number/positive. Any way around this? :)
    By Elijah in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-26-2018, 01:01 PM
  2. [SOLVED] Mystery - Excel treats the same cell contents differently
    By Dale in forum Excel General
    Replies: 7
    Last Post: 11-09-2015, 01:59 AM
  3. Excel 2010 treats dates differently to excel 2013
    By HughTower in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-08-2015, 07:22 AM
  4. When copying Excel formulas, can you swap around the way Excel treats columns & rows?
    By TCStephens in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2014, 09:34 PM
  5. Outlook treats first email created by Excel differently
    By blackworx in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-15-2012, 08:32 AM
  6. Excel wrongly combined numerous data into one cell
    By hd3017 in forum Excel General
    Replies: 1
    Last Post: 01-09-2012, 11:06 PM
  7. Excel 2007 : excel data when mail merged displays wrongly
    By johnmaria09 in forum Excel General
    Replies: 0
    Last Post: 11-28-2008, 01:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1