+ Reply to Thread
Results 1 to 5 of 5

2 Cells Of Excel Having Same Value Is Not Matching With Each Other

  1. #1
    Registered User
    Join Date
    03-09-2020
    Location
    Vadodara
    MS-Off Ver
    2010
    Posts
    2

    2 Cells Of Excel Having Same Value Is Not Matching With Each Other

    Happy Morning Respected Sir/Mam,

    I am facing an issue where I have 2 columns in which many cells have same value to next column but it excel match functions or exact function didn't work.

    I have tried using (Trim, Clean, Removing spaces and hidden characters) but it is still not working for me. I have data in Column A as well as in Column B. I also applied =a2=b2 but didn't work again.

    Will you please guide me.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: 2 Cells Of Excel Having Same Value Is Not Matching With Each Other

    You can test using following.

    In C2: =MID($A$2,COLUMN(A1),1)
    Copy across till Column BD.

    In C3: =MID($B$2,COLUMN(A1),1)
    Copy across till Column BD.

    In C4: =C2=C3
    Copy across till Column BD.

    You'll see that some characters are not exact match.
    Specifically, 'і' and 'i'. First one is Unicode 1110 (CYRILLIC SMALL LETTER BYELORUSSIAN-UKRAINIAN I), latter is standard small case I (Unicode 105).
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: 2 Cells Of Excel Having Same Value Is Not Matching With Each Other

    Oh, to make A & B to be equal, you can use SUBSTITUTE function.

    Ex:
    =SUBSTITUTE(A2,UNICHAR(1110),CHAR(105))=SUBSTITUTE(B2,UNICHAR(1110),CHAR(105))

    However this only works with Office 365. In your case replace UNICHAR(1110) with actual character.

    Ex:
    =SUBSTITUTE(A2,"і",CHAR(105))=SUBSTITUTE(B2,"і",CHAR(105))

  4. #4
    Registered User
    Join Date
    03-09-2020
    Location
    Vadodara
    MS-Off Ver
    2010
    Posts
    2

    Re: 2 Cells Of Excel Having Same Value Is Not Matching With Each Other

    Hello Sir,

    Thank you so much for providing solution to this request. I appreciate your valuable time and efforts.

    Will you please let me know the process as well that how you detect that where was the problem? Like in this case the problem was with the letter "і".

    How can I identify or test that in which letter or word the problem exists due to that the values does not match?

    I am giving below 1 more example which is not matching, there could be many more cases, I just wanted to know that how can I test or detect the problem.

    - - - - - твердi,*в iншiй упаковцi; рiдкi

    - - - - - твердi, в iншій упаковці; рiдкi

    These 2 values are not matching.

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: 2 Cells Of Excel Having Same Value Is Not Matching With Each Other

    Just follow similar steps to what I did in post#2. It's method to compare character one by one.

    But at quick glance, two strings are not equal. As first one has "*" but second one doesn't.

+ 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. Excel reads blank cells as matching
    By cnbales in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-29-2016, 03:31 PM
  2. Excel vba macro for multiple matching cells detection
    By adrian787 in forum Excel General
    Replies: 2
    Last Post: 03-25-2015, 05:54 AM
  3. Excel Matching Cells Formula
    By Pope_003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-17-2014, 04:31 AM
  4. Help matching cells and then looking at other column based on those matching cells
    By Ppessina in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2013, 02:37 PM
  5. Replies: 3
    Last Post: 02-23-2012, 12:17 PM
  6. Replies: 6
    Last Post: 05-06-2010, 10:04 PM
  7. Align Matching Cells in Excel
    By rej1969 in forum Excel General
    Replies: 3
    Last Post: 01-20-2009, 03:59 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