Left Anti/Right Anti not giving the expected results.

[bpfish36]

I have two worksheets; one with 63,181 rows and the other with approximately 60,000 rows. I would like to find the matching records (rows) from both sheets, the records found only in Sheet 1 and those found only in Sheet 2. I've created a connection only for both sheets in Queries & Connections. I right click Sheet 1, select 'Merge" and select both sheets. I then select the 3 columns (Date, Name, Amount) that would produce matches or lack thereof. I run an Inner Join to get (hopefully) the records that appear in both, Left Anti to get those in Sheet 1 only and Right Anti to get those in Sheet 2 and not Sheet 1. When I run the Inner I get 47,140 results, the Left gives me 15,982 and Right gives me none. My expectation was the number of matched results plus the number unique to one sheet would equal the total for that same sheet. So the total for the Inner and the Left should be 63,181 as this would be the sum of those in both plus those only in Sheet 1. Instead its 63,122 (47,140 +15,982). My assumption was blank rows in the Excel worksheet, and there are blank rows (see my previous query in this forum) but there aren't 59. Additionally, with the Right Anti giving zero results that's not even close to the number in Sheet 2. Can somebody please tell me what I'm doing wrong? Also, will blank cells affect the process? For example, for some records there is a date and an amount, but no name. If both sheets have the matching date and amount, and neither sheet has a name, will that be returned as a match? Any help at all is MUCH appreciated! Thank you.

 

[sandy666]

post representative example and expected result but not 60000 records (use XL2BB)
or
link to the shared excel file via googledrive, onedrive, dropbox or any similar service

 

[bpfish36]

Thank you Sandy. I'm having trouble getting the XL2BB add-on to load. The special tab showed up once and then crashed Excel, when I re-opened it the special tab was gone, but Mr. Excel still shows as an active add-on. BTW, you helped me out last night, do you get a credit for that, if so, how do I make sure you got the credit?

 

[sandy666]

I'm having trouble getting the XL2BB add-on to load

so use second option
----------------------------------------------

how do I make sure you got the credit?

at the bottom of specific post you'll see "credit"

btw. in the future use the proper forum

Power Tools

Power BI, Power Pivot, Power Query (Get and Transform), Power View, and Power Map

www.mrexcel.com

 

[bpfish36]

Okay, I switched it over. I'm trying to find the right range to replicate the issue. Of course, when I choose a smaller range it works perfectly!

 

[sandy666]

Don't duplicate posts
did you read

Message Board Rules

Along with the Terms of Use you agreed to follow when registering, please follow these rules.

www.mrexcel.com

worth reading to avoid any troubles later
----------------------
ok, you need to find representative example with improper behaviour