Compare Data, if equal then cell = yes

February 7, 2012 at 12:55:55
Specs: Windows 7
I'm looking for a down and dirty, quick to the punch solution for comparing two workbooks and placing data in a cell if there is a match.

The premise is, one workbook is our companies Exchange Database in CSV format, and the other is our HR Departments employee list. Some employees have email access, and some don't. I have a new column in that HR Book that is simply titled "Has Email"

So What I want to do is take the data from Book1 (Exchange) and compare it cell by cell to Book2 (HR) and if it finds a match, get the Book2 row number, and in column H, input "Yes"

I'm not very Macro Savvy, so I'm not even sure if this is possible? If so, I would imagine that Down and Dirty, and Quick isn't an option here..

Keeping in mind, that row positions will NOT match.. For example.. One name is on row 15, and the match is on row.. 22


See More: Compare Data, if equal then cell = yes

Report •


#1
February 7, 2012 at 13:42:28
A =VLOOKUP() should be able to get you what you need.

How is your data set up in the two books?

MIKE

http://www.skeptic.com/


Report •

#2
February 7, 2012 at 13:57:22
You'll have to clarify that for me.. Like I said, I'm macro/VB Challanged :)

As far as the workbooks are concerned, in the Exchange book, there are 2 columns. "Display Name" which contains First name Last name, no separator (except a space) and in the HR Book, there are 3 columns: First Last, Location, and ClockCode (Time Clock Codes based on location..) actually there are 4 columns now, since I added the "Has Email" column


Report •

#3
February 7, 2012 at 14:23:16
So your books look like:

Book 1 - Exchange Book			
      A	          B		
Display Name   ???????			
			
			
Book 2 - HR Book			
      A	       B	C 	    D
First Last Location  ClockCode	Has Email?


in the Exchange book, there are 2 columns.

What is in the second column?
You have Display Name & what else?

Are the names in both books entered in the same fashion, First<space>Last
with no spelling errors?

get the Book2 row number, and in column H, input "Yes"

Where does column H come into play???

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 7, 2012 at 14:28:07
Sorry I should have clarified more..

EXCHANGE BOOK:
A B
Display Name Primary SMTP Address


HR BOOK
A B C F G H
First Last Location Clock Number Has Email


Not Sure where D and E went, as I didn't create that particular book, but F and G are spacers.

EDIT: Sorry, don't know how to format it so it fits right...



Report •

#5
February 7, 2012 at 14:53:27
Now how do you determine if an employee Has email?

Does column B contain a value?

MIKE

http://www.skeptic.com/


Report •

#6
February 7, 2012 at 15:13:36
Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
February 8, 2012 at 06:21:39
Ok, let me see if I can get this.. close enough

Exchange Book:
         A                   B
Display Name          Primary SMTP Address


HR BOOK
   A          B       C         F G    H
First Last Location Clock Number    Has Email

To answer the questions, Display name from the Exchange book and First Last in the HR Book (so both books, column A) should match, and if they are in the Exchange book, then they have email, as that is a dump of our Exchange Users Table.

EDIT: Missed a question - To my knowledge, the spelling is identical on both sides. The only discrepancies that there MAY be are in the cases of nicknames.. Sometimes we put, for example.. Margie for Marjorie, because that's what everyone calls her. However, those are few and far between, and I know what names to seek out manually on that front.


Report •

#8
February 8, 2012 at 09:16:05
So all we have to do is Match Names;

If your data looks like this:

Exchange Book

            A            B
1) Display Name	     SMTP Address
2) Smith John	
3) Jones William	
4) Public John Q.	

HR Book

       A               B        C          D
1) First Last	   Location  ClockCode  Has Email?
2) Smith John			
3) Public John			
4) Jones William			

In Column D of the HR book enter the formula:

=IF(ISNA(MATCH(A2,[Book1_Exchange.xlsx]Sheet1!$A$2:$A$4,0)),"NO","YES")

You will need to modify the formula with the correct book and sheet names.

Drag down as many rows as needed.

You should get:

HR Book
       A               B        C          D
1) First Last	   Location  ClockCode  Has Email?
2) Smith John			           YES
3) Public John			            NO
4) Jones William			   YES

Notice that John Public is listed as not having email.
That is because the names do not match exactly.
The Exchange book has Public John Q
The HR book has just Public John

That is one of the things you will have to watch out for.

MIKE

http://www.skeptic.com/


Report •

#9
February 8, 2012 at 09:43:59
Thank you, but it returned NO on my test when I plugged it into one I knew was a match.

Can you break the code down so I can see if I can try to troubleshoot it, without constantly pestering you guys? (basically what does what)


Report •

#10
February 8, 2012 at 10:10:28
one I knew was a match.

It may Look like a match but the most common reason for an error
is a space character after the name
or two space characters between the names.
or there could be unprintable characters embedded in the cell.

Try this:

Insert a new column next to your name column and enter this formula:

=TRIM(CLEAN(A2))

With cell A2 being the cell with the name in it.
Drag down as many rows as needed.

This will remove any hidden spaces or special characters.

Then Select the new names and do a Copy / Paste Special / Values

See if the cleans up your name cells.

Do it on both Exchange & HR sheets.

MIKE

http://www.skeptic.com/


Report •

Ask Question