Compare 2 columns and sum frequency of duplicates

1. Compare 2 columns and sum frequency of duplicates

I have X users ID and some of them are being used by more than 1 person.

A ......... ......... B
UserId01 ....... Person01
UserId01 ....... Person02
UserId02 ....... Person03
UserId03 ....... Person04
UserId03 ....... Person05

What do I need? Count how many UserId I have, how many people I have and if the numbers are equal so there's person per userid. But if they're different 2 or more people are using the same userid.
That's why I need to compare A and B columns in order to list these people that are using same userid.

Example: UserId03 is being used by Person04 and Person05.

2. Re: 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?

3. Re: Compare 2 columns and sum frequency of duplicates

Sample inserted. I must learn pivotable first

4. Re: Compare 2 columns and sum frequency of duplicates

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.

5. Re: Compare 2 columns and sum frequency of duplicates

Well, first time I was able to download your file MikeTRON. But now when I click on Sample.xlsx it keeps sending me to the login page and then show me this: "Nice to see you again! Thank you for logging in, jntslvdrt.Click here if your browser does not automatically redirect you." then send me back to login page again and this keeps happening over, over and over... :S

6. Re: Compare 2 columns and sum frequency of duplicates

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.

7. Re: Compare 2 columns and sum frequency of duplicates

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.

8. Re: Compare 2 columns and sum frequency of duplicates

Now I put more data. But now I want to sort who has more users under its name. Like the first one into the 4th and last pivottable Grant Rodgers he has only one user under his name... while Grady Payne has 3. So I want to filter in order to put Grady Payne and other like him (1+ users under his name). I made some attempts using filter but with no success...

Sorry, Sample2 is the correct one

9. Re: Compare 2 columns and sum frequency of duplicates

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.

Check it out here:
http://www.powerpivotpro.com/2011/07...2-much-faster/
http://chandoo.org/wp/2013/02/11/dis...-pivot-tables/

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1