Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Compare Cells

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Andy Devine
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Bit of a confusing one this..
    I have 8 columns. In the 9th column i want to include a formula that compares the contents of the 8 cells to the left..if they are the same to say "same" if not the same to say "not the same"
    Complication: if one of more of the cells says, say, "exclude" then the formuala should exclude these cells

    Example using 4 columns (remember there are 8 on my sheet):

    A 1 = ABC, B1 = ABC, C1 = ABC, D1 = ABC: E1 = "Same"

    A 2 = ABC, B2 = ABC, C2 = ABC, D2 = EXCLUDE: E2 = "Same"

    A 3 = ABC, B3 = ABC, C3 = ABCDEF, D3 = EXCLUDE: E1 = "Not Same"

    Any help would be appreciated, thanks Andy

    Andy Devine

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,075
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-07 08:01, Andy D 2002 wrote:
    Hi
    Bit of a confusing one this..
    I have 8 columns. In the 9th column i want to include a formula that compares the contents of the 8 cells to the left..if they are the same to say "same" if not the same to say "not the same"
    Complication: if one of more of the cells says, say, "exclude" then the formuala should exclude these cells

    Example using 4 columns (remember there are 8 on my sheet):

    A 1 = ABC, B1 = ABC, C1 = ABC, D1 = ABC: E1 = "Same"

    A 2 = ABC, B2 = ABC, C2 = ABC, D2 = EXCLUDE: E2 = "Same"

    A 3 = ABC, B3 = ABC, C3 = ABCDEF, D3 = EXCLUDE: E1 = "Not Same"

    Any help would be appreciated, thanks Andy
    Array-enter and copy down:

    =(SUM(IF(A1:D1<>"EXCLUDE",1/COUNTIF(A1:D1,A1:D1)))=1)+0

    This gives you 1 when same, 0 when not same.

    If you rather want to see "same" and "not same", again array-enter;

    =IF(SUM(IF(A1:D1<>"EXCLUDE",1/COUNTIF(A1:D1,A1:D1)))=1,"same","not same")

    To array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Andy,

    This would be easy to write a custom UDF to do. I expect that one of the resident worksheet function gurus will come up with a simple solution shortly, but if not let me know and I'll provide the UDF.

    Damon
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 08:26, Aladin Akyurek wrote:
    =(SUM(IF(A1:D1<>"EXCLUDE",1/COUNTIF(A1:D1,A1:D1)))=1)+0
    wow, that's elegant

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •