Thanks:  0
Likes:  0

# Thread: Compare Cells

1. 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

2. 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. 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

4. 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

## User Tag List

#### Posting Permissions

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