Compare Cells

Andy D 2002

Board Regular
Joined
Apr 22, 2002
Messages
106
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top