+ Reply to Thread
Results 1 to 9 of 9

Compare 2 columns and sum frequency of duplicates

  1. #1
    Registered User
    Join Date
    03-23-2015
    Location
    São Bernardo do Campo, Brazil
    MS-Off Ver
    2007
    Posts
    5

    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. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    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?
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    03-23-2015
    Location
    São Bernardo do Campo, Brazil
    MS-Off Ver
    2007
    Posts
    5

    Re: Compare 2 columns and sum frequency of duplicates

    Sample inserted. I must learn pivotable first
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    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. #5
    Registered User
    Join Date
    03-23-2015
    Location
    São Bernardo do Campo, Brazil
    MS-Off Ver
    2007
    Posts
    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. #6
    Registered User
    Join Date
    03-23-2015
    Location
    São Bernardo do Campo, Brazil
    MS-Off Ver
    2007
    Posts
    5

    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.
    Last edited by jntslvdrt; 03-24-2015 at 09:25 AM.

  7. #7
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    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. #8
    Registered User
    Join Date
    03-23-2015
    Location
    São Bernardo do Campo, Brazil
    MS-Off Ver
    2007
    Posts
    5

    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
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,048

    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.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Compare 2 columns and remove duplicates in both columns when found
    By 1dtms in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2013, 02:19 AM
  2. [SOLVED] Macro to Compare Columns A & B and dispaly any duplicates in Columns C & D
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 02:24 AM
  3. Compare two columns delete duplicates
    By camo090 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-16-2013, 03:59 PM
  4. Compare data across 7 columns for duplicates
    By rstellers in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2010, 09:19 AM
  5. [SOLVED] compare two columns and remove duplicates
    By Moni39 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-05-2005, 02:06 PM

Bookmarks

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