Compare Rows in 2 columns and count those that differ

**Tim3057** · 03-12-2007, 08:44 AM

Can I compare 2 columns (that are not side by side) and return a count of the number of times they differ?

ie

apples pears
apples apples
apples cherries
apples apples

would return a count of 2..?

I need this to be a formula in one cell, with an answer showing in that cell.

This may be basic stuff, but that's my level!!!

Thanks,

Tim

**royUK** · 03-12-2007, 09:13 AM

Try
Option Explicit

Please Login or Register  to view this content.

**Tim3057** · 03-12-2007, 09:18 AM

Thanks... but I'm just after a single fomula to put in a cell on the spreadsheet.. I'm not trying to put it in a program - am I in the wrong forum?

**Bryan Hessey** · 03-15-2007, 08:58 AM

Originally Posted by Tim3057

Thanks... but I'm just after a single fomula to put in a cell on the spreadsheet.. I'm not trying to put it in a program - am I in the wrong forum?

Hi,

try

=COUNTA(A:A)-SUMPRODUCT(--(INDIRECT("A1:A"&COUNTA(A:A))=INDIRECT("B1:B"&COUNTA(A:A))))

hth
---
added,

just read the original post, and the wording does not agree with the data sample given.

What is meant by ' (that are not side by side)' in relation to where are they???
---

Compare Rows in 2 columns and count those that differ

LinkBack

Thread Tools

Rate This Thread

Display

Compare Rows in 2 columns and count those that differ

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions