Compare data in multiple columns across 2 worksheets

Re: Compare data in multiple columns across 2 worksheets

Okay, so attached is the scrubbed file. A note about what you're about to see... there are two worksheets with nearly identical information. Except the worksheet titled "TBP" has values in Column Z which are the most current. I'm trying to get those values into the right coordinates on "Sheet 1." The criteria I decided to use was the find a match for the name, the CPT, and the date, and if all that matches, then show the right value (TBP!Zn) somewhere in Sheet 1.

Also I believe JohnnyBeck solved it with his Macro. Upon further inspection, I found certain cells in Column Z of Sheet 1 to be have the correct value. THANK YOU but I am still interested in learning other ways of attacking this problem. And Johnny I'm hoping you take a look at this too, I must admit, though it appears your macro works a very small number of cells were changed (maybe 50 out of nearly 3,000) and I was expecting far more... in fact, its hard for me to tell whether I copied those over having the right value or whether the macro worked on them. Once all this checks out I can go ahead and roll it out for the rest of the files (another 127k lines, give or take 2... )

THANKS YOU GUYS!

Re: Compare data in multiple columns across 2 worksheets

Okay, so attached is the updated Scrubbed sheet with the cleaned up dates that no longer have times in them. And for JohnnyBeck, I do see where I told you names were in Column C, my apologies, a typo on my part. Names are in U, dates in C, codes in E.

Re: Compare data in multiple columns across 2 worksheets

And for Kris, I, like you used =INT to change the values of Column C. That cleaned up list is in the file attached to my previous post. Not sure if that helps but it did show #N/A when I tried the formula (both in clean and uncleaned versions of column c).

Re: Compare data in multiple columns across 2 worksheets

Quote from JohnnyBeck;560446

Ok. This code works with your layout. However, be aware that I had to remove the timestamps in order for the dates to "match" since I'm comparing values of cells. I did that quickly using Text To Columns on the data tab in Excel. Also, on your example sheet, there were NO matches of data. I had to create a match or two to make sure the code was working with your layout. Either way, here it is:

Code

Sub multmatch()


Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long


totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$U$2:$U$" & totalrows
 
For Each cell In Sheets("Sheet1").Range(nameCol)


Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)


For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, -16) & found.Offset(0, -18) Like cell & cell.Offset(0, -16) & cell.Offset(0, -18) Then
cell.Offset(0, 5).Value = found.Offset(0, 5).Value


End If
Next found
Next cell


End Sub

Display More

THANKS!!!!!!! It works swimmingly (that's a new compliment, right?! j/k). I was wondering if there is a way to have it highlight the cells it changed. In the scrubbed file I provided the TBP sheet has the cells highlighted in yellow, can that same highlighting be transferred over with the macro when the cells are altered?

Re: Compare data in multiple columns across 2 worksheets

Actually about 20-30 minutes after sending you that message I found my error and corrected it, tried to send you a PM but your inbox was full. I did start running tests of the code, so far I can't tell if there's a difference (the count of highlighted boxes remains the same after executing the macro) but I'm not done running through the data sets. Prior to PMing you about the yellow highlighting I did go back and edit many of these manually, but not sure where I started or stopped so I'm sure that's distorting the apparent effects of the macro. I will keep you posted!

Re: Compare data in multiple columns across 2 worksheets

I ran the macro across the five data sets that I needed to and there was no change (at least in bg color) on any sets. Set 1 originally had 59 highlighted cells, after the macro, still 59; Set 2 had 989 pre macro, 989 post macro highlights; Sets 3 thru 5 had 0 cells highlighted pre macro, 0 cells highlighted post macro, here is the macro as it ran:

Sub multmatch()
Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$U$2:$U$" & totalrows
 
For Each cell In Sheets("Sheet1").Range(nameCol)
Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)
For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, -16) & found.Offset(0, -18) Like cell & cell.Offset(0, -16) & cell.Offset(0, -18) Then
cell.Offset(0, 5).Value = found.Offset(0, 5).Value
cell.Offset(0, 5).Interior.Color = 65535
End If
Next found
Next cell
End Sub

Re: Compare data in multiple columns across 2 worksheets

No, you were right. I had to adjust the dates using INT() and it worked. Thank you so much!