Compare data between sheets

**Sniper** · 05-07-2010, 01:33 PM

I have areal brain twister here, I tried everything but just can not get this to work. I am trying to compare 2 worksheets so it shows me the diff on a 3rd sheet. I have attached an example of what I mean.

Thanks

**protonLeah** · 05-07-2010, 08:02 PM

Don't understand why you laid out the compare sheet the way you did. I redid it and sorted the data sheets, the compare sheet uses VLOOKUP:

**Sniper** · 05-08-2010, 03:49 AM

Thanks a lot works great

**Sniper** · 05-08-2010, 02:33 PM

I had a previous post about this but at first I thought the help given would work but after 8 hours I now see it's not the way to go. I have 2 sheets that I need to compare withe each other, the biggest problem is that there are a lot of duplicates that shouldn't be removed so the vlookup function doesn't work. I did attach a new example.

**jaslake** · 05-08-2010, 02:48 PM

Hi sniper

What are the markers you're comparing on, only column A of Data1 and column A of Data2? I could probably figure it out if I studied your data but then I'd only be guessing.

John

**shg** · 05-08-2010, 02:58 PM

Another problem with your data is that the names have leading spaces, trailing spaces, and inconsistent spaces, e.g., "MANYAKA MP " and "MANYAKA M P".

**jaslake** · 05-08-2010, 04:24 PM

Hi sniper

Assuming your key is only column A, the code in the attached workbook appears to do as you require. Let me know of issues.

John

**shg** · 05-08-2010, 04:30 PM

Just for other members, the code requires Excel 2007+.

**jaslake** · 05-08-2010, 04:41 PM

Ha!!! Burned again. I usually do testing in Excel 2000; don't know why I didn't this time. Oh well. Here's the code for Excel 2000 and later.

Please Login or Register  to view this content.

John

**jaslake** · 05-08-2010, 04:42 PM

Thanks shg

John

**Sniper** · 05-09-2010, 12:57 AM

You guys are great it's PERFECT!!!!!!! Thanks a lot

**Sniper** · 05-11-2010, 02:36 AM

Hi Guys

Once again I need your help, This code works great only problem I have is that when there are a lot of the same client no it does sort the amounts at the same place. I did play around with the code but can't find a way for it to sort it accordingly. Any ideas on how to fix this??

I did add a workbook to show what I mean.

Thanks