# 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!

## Files

• Re: Compare data in multiple columns across 2 worksheets

Is the second sheet longer than the first?

Also, I thought you said you wanted column Z from the second sheet to be placed in column AN on the first sheet. Is it the other way around?

I just looked at your scrubbed sheet. Dude, the names are not in column C as you said they were.

Am I now looking at an accurate picture of the sheet you want? Are you wanting to use the names in column K to be the basis of the comparison??

I can do this, but I need accurate directions bro.

• Re: Compare data in multiple columns across 2 worksheets

Hi,

Try this one.

=LOOKUP(2,1/((INT(TBP!\$C\$2:\$C\$2484)=C2)*(TBP!\$E\$2:\$E\$2484=E2)),TBP!\$Z\$2:\$Z\$2484)

Col C in sheet1 has only dates, where as Col C in TBP has date and time.

• Re: Compare data in multiple columns across 2 worksheets

I'm out.

• 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.

## Files

• 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

I'm working on it for you now. Am I correct that you now want the Paid Amount from column Z to be updated from column Z on the TBP sheet?

• Re: Compare data in multiple columns across 2 worksheets

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:

• 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:

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

Code
``````Sub multmatch()Dim cell As RangeDim found As RangeDim nameColDim totalrows As Longtotalrows = ActiveSheet.UsedRange.Rows.CountnameCol = "\$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) Thencell.Offset(0, 5).Value = found.Offset(0, 5).Valuecell.Offset(0, 5).Interior.Color = 65535
End IfNext foundNext cellEnd Sub``````
• Re: Compare data in multiple columns across 2 worksheets

I don't know what happened with the code window...but I added the coloring line to the code for you. Try that out.

• 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:

• Re: Compare data in multiple columns across 2 worksheets

I don't know what to tell you friend. It works on my computer. Don't forget, you have to make sure that your dates don't have the timestamp in them, or it won't find a match. Other than that, I'm at a loss. Very sorry.

• 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!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!