Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Comparing Columns using VBA

  Asked By: Anubis    Date: Aug 25    Category: MS Office    Views: 898
  

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

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Alyssa Campbell     Answered On: Aug 25

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]

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
A1:D4.

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: Shelia Wells     Answered On: Aug 25

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

 
Answer #3    Answered By: Roop Kapoor     Answered On: Aug 25


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.




Tagged: