MrExcel Publishing
Your One Stop for Excel Tips & Solutions

comparing ranges


Posted by jeff on February 05, 2002 11:24 AM

hi
how can i compare 2 ranges in excel and output the difference in a new cell range? cells are formatted as text. thanks in advance


Posted by Mark W. on February 05, 2002 11:29 AM

Provide a small set of sample data and the desired result [NT]

Posted by jeff on February 05, 2002 11:38 AM

Re: Provide a small set of sample data and the desired result [NT]

A1:A3 b1:b3 c:
abc abc bac
bac bca cab
cab acb

basically anything that doesn't appear in range b1:b3 will be output to range c:

Posted by Mark W. on February 05, 2002 11:49 AM

Re: Provide a small set of sample data and the desired result [NT]

Suppose A1:B4 contains...

{"Field1","Field2"
;"abc","abc"
;"bac","bca"
;"cab","acb"}

...cell C1 contains "Field1" and cell E2 contains
the formula, =A2<>B2.

1. Select A1:B4 and choose the...
Data | Filter | Advanced AutoFilter... menu command.
2. Enter $E$1:$E$2 as the "Criteria range"
3. Click the "Copy to another location" radio button
and enter $C$1 in the "Copy to" field.
4. Press [ OK ].

Posted by jeff on February 05, 2002 12:04 PM

Re: Provide a small set of sample data and the desired result [NT]


thanks mark