+ Reply to Thread
Results 1 to 4 of 4

Comparison for two columns that have text values

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

    Comparison for two columns that have text values

    Hi All,

    Hope you are doing well!...I am trying to compare text values in two columns...If the values are same I return 1 otherwise I return 0...But I have problems with the return '0'...I get '0' return values even if ID's are matching...Can you please help with the same...Attached the file...

    Thanks,
    Arun
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,846

    Re: Comparison for two columns that have text values

    that is because the one is text and the other is numeric. A36 is numeric and B36 is text. you could change your formula to this and it will work. =IF(A2*1=B2*1,1,0)

    EDIT: also not sure how you want row 100 handled where you have 210 and 00210, that formula will make the leading zeros irrelevant and so equal.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

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

    Re: Comparison for two columns that have text values

    Hi Sambo,

    I want to treat the leading zeros as relevant too... so 210 and 00210 should be treated as different...can you please advice on how i can do that...

    Thanks,
    Arun

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,846

    Re: Comparison for two columns that have text values

    this one is a bit more convoluted but treats rows 81 and 100 differently d/t length... =IF(AND(LEN(A2)=LEN(B2),A2*1=B2*1),1,0)
    see what you think.

+ 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] Count duplicate text values in columns whilst ignoring/excluding certain values
    By adamwestwell in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2017, 05:34 AM
  2. 4 Columns Comparison
    By AndrewDps in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2016, 07:45 AM
  3. [SOLVED] Formula or VBA - Comparison CSV Values between 3 cells for used range. Find unique values.
    By grphillips in forum Excel Programming / VBA / Macros
    Replies: 36
    Last Post: 06-09-2014, 02:12 PM
  4. Columns comparison
    By girish1989 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-27-2013, 12:59 AM
  5. Comparison between 4 columns
    By johnmathew in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-09-2012, 01:59 AM
  6. Problems searching Excel cells for InStr text comparison, cant get cell values ...
    By BaLLZaCH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-27-2008, 05:52 AM
  7. Text comparison between multiple columns
    By phanh in forum Excel General
    Replies: 1
    Last Post: 03-16-2008, 03:24 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