MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Comparing Columns using VBA

  Asked By: Norma    Date: Oct 17    Category: MS Office    Views: 1252

i am have 2 sets of data (a company name
and an email address) and i am trying to write a script that will check
that each email address in the 1st set of data is equal to the email
address in the second set of data and if not to highlight it. (all the
data is muddled so i cant even sort and check but i there are 4 sep
columns [2 emails, 2 names]

i have tried using loops and formatting but it doesnt seem to work. is
it because it is txt as opposed to numbers?

Could someone please help me figure out this otherwise i am going to
have to manually check 2000 email addresses.



3 Answers Found

Answer #1    Answered By: Alfonsine Miller     Answered On: Oct 17

What are "sep columns"?

You could use conditional formatting  for this. For this
example, I'll use some sample data  filling 4 rows. Open a new
sheet. Add the following conditional formats to cell A1 and C1.
(Select the "Formula Is" option and paste these formulas.) Set the
format to your normal background. Set the background for A1:D4 to
be highlighted.

A1 => = MATCH( $A1, $C$1:$C$4, 0 ) > 0
C1 => = MATCH( $C1, $A$1:$A$4, 0 ) > 0

Select cell A1 and use the + cursor to drag the cell to B1. Do
the same for cells C1 and D1. Now, select A1:D1 and use the +
cursor to fill 4 rows. Copy and paste these values only into range

foo foo bar foo
bar foo bar foo
qaz bar car bar
qar bar foo bar

The matched email  addresses will be normal while the unmatched
cells will be highlighted.

: i have tried using loops  and formatting but it doesnt seem to
: work. is it because it is txt  as opposed to numbers?

Without seeing your code, it is pretty much impossible to tell
what may be wrong with it.

Answer #2    Answered By: Fedde Bakker     Answered On: Oct 17

Try vlookup function.........................

Answer #3    Answered By: Taylor White     Answered On: Oct 17

Have you tried Countif function.
Bring both the databases in one sheet & try Countif function in adjucent column.
OR use conditional formatting  using countif formula. F1 help  provides good
example for using countif function.

Didn't find what you were looking for? Find more on Comparing Columns using VBA Or get search suggestion and latest updates.