Compare 2 columns and sum frequency of duplicates

I would make two pivot tables. One to look for oddities in column A, and one for Column B.

Can you post sample data?

So I created 4 pivot tables from the data on Sheet1.

I brought in the username and then the COUNT of the Full Name. Then I sorted by Count of full name from large to small (so the ones with more than 1 will be at the top).

I then created another pivot that shows what Full name fall under what username so you can see exactly what are causing the duplicates.


I then did the exact same thing but started with full name and brought in the username. sorted by count, and showed you which were causing duplicates.


Sample.xlsx

Pivot tables are awesome for churning through big data, and I would say they are probably the most powerful tool excel offers, so they are worth learning.

How have you eliminated duplicates from that big list? Just by going to Data tab > Remove duplicates > and selecting USUARIO and Nome Completo > OK. Was that? Because I can't simply delete them from data source.

A pivot table naturally only lists the unique items in the list, so it didnt DELETE it, it just doesnt show them more than once.

So it seems you are looking for a DISTINCT count?Grant Rodgers shows 284 usernames (that are all apparently "220-PAULO" so that 284 is inflated.

The easiest way to capture DISTINCT count is with PowerPivot(a free microsoft addin/download).

Check it out here:
http://www.powerpivotpro.com/2011/07...2-much-faster/
http://chandoo.org/wp/2013/02/11/dis...-pivot-tables/
https://www.youtube.com/watch?v=1YJ0GMFm7IM

With that being said, I cant really build you a model with my version of excel as they are not backwards compatible.
So you can do the following.
You can just include a count to the right of each of the base pivots to use AS the data for another pivot to find what you want, but its not very pretty (but it does work).
Attached is what I mean.

Sample2.xlsx