Results 1 to 11 of 11
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting duplicates (Excel 2003)

    I need help with something that should be simple. I have lists of names and companies from a web log. Many of these are dups (many people from the same company so its listed unique by person). I want to count the # of times a given company has visited the site. I have a list like this:

    <pre>JoeM CompanyAA 1
    JackC CompanyAA 3
    MarkL CompanyBB 1
    SueB CompanyCC 2
    DebS CompanyCC 5</pre>

    I want the output (my count) to be CompanyAA=4, CompanyBB=1, CompanyCC=7. I can get the unique list using Advanced Filter but also need to count the number of times anyone from that company came to the site.

    Thnx,
    Deb

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    Enter your data in A1 to C5.

    Then try:

    =SUMIF($B$1:$B$5,"AA",$C$1:$C$5) in a new cell.

    Copy this down 2 cells and change to "BB" and "CC" in the new cells.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    Hey that worked great! I had tried SUMIF but couldn't get it quite right. Now I need to make it work such that I don't have to use Advanced Filter to find the unique records beforehand.

    Thnx,
    Deb

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting duplicates (Excel 2003)

    You can create pivot table of the data and this will extract the unique items and also do multiple stats. The advantage is that if the data is changed, the pivot table can be refreshed and it will update.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    Actually, Advanced Filter just "hides" the rows that don't match your criteria. So you should be able to use the SUMIF formuala that I sent you, expanding the 2 ranges to include all the rows in the full downloaded list. The results should be the same, regardless of whether some rows are hidden or not. Adv Filter, as I understand it, is mainly a "visual effect" -- all the data is still there, with some of it showing. If this is incorrect, please advise.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting duplicates (Excel 2003)

    Advanced Filter has several "modes". The default is to filter the records in place, i.e. hide the rows that don't match the selection criteria. But it is also possible to copy the filtered data to another location, with the option to copy unique records only (see screenshot),
    I assume that jujuraf used the latter option to create a list of unique values, and based the formulas on this list.

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    Yes I should probably delve into pivot tables (up until now I've avoided them since I've never had a need and I haven't bothered to learn how to use them well). This is a report we need to do quarterly and the manual effort to do it is ridiculous. I was going to do some VBA to search for the URLs, count, etc. as the original data dump is 1000's of rows of data, much of which is to be ignored.

    Thanks for the advice to all who replied. It helped a lot.
    Deb

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting duplicates (Excel 2003)

    Creating a pivot table for this purpose is very simple - you don't need to be a "pivot table expert".
    - Make sure that there are field names (column headers) above the data.
    - Click in any cell of the table.
    - Select Data | PivotTable and PivotChart Report...
    - Click Next > twice.
    - Click Layout...
    - Drag the company name field to the row area.
    - Drag the visit count field to the data area.
    - Excel should automatically select Sum as operator; if not, double click the data field and select Sum.
    - Click OK.
    - Specify where you want the pivot table - in a new worksheet or in an existing one.
    - Click Finish.

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    DANG!! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> this is so great!! I now am super motivated to learn more about pivot tables. I got roped into this web site visitor log project at the last minute after the person spent 12 hours. She knows almost nothing about Excel (no formulas) and when I saw that she manually counted things (to total # of users who clicked a given link, I cringed <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> and wanted to help her do it faster next time. This is a once/quarter report and the web reporting tool outputs a .cvs file and she imports into Excel. After that, her Excel usage drops to virtually nothing except Find, Copy, Paste (repeat 100 times) to put related data on their own sheet.

    I had showed her some formulas but hadn't really gotten into the details about what she needed until now. I had her use Advanced Filters but with the pivot table she doesn't even need that.

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    Deb

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting duplicates (Excel 2003)

    Hi again, One more follow up on this same subject if I can.... In using the pivot table I discovered that I need to do something else and can't figure out how to get the data.

    In the web site log, I have lists of URLs and names/emails of people who visited and their company names. I find that the same person can be seen going to different URLs (each doc has its own URL). What I need is to count a person ONCE no matter that they download 5 docs or just 1 doc. The person is counted 6 times in the pivot table.

    I need something like Company for rows, and unique visitors as a total (data) but I don't want these people counted more than once. The data is listed per document (URL) which is itself a child of the parent site of course. So how can I extract the # of unique visitors per page and count them once?

    URL-a for PageAAA
    [userID, company, total unique visits]
    UserBob, AOL, 5
    UserSue AOL, 2
    UserMike, AT&T, 2

    URL-b for PageBBB
    [userID, company, total unique visits]
    UserBob, AOL, 2
    UserJoe, AT&T, 6

    So here I want to say that URL-a has 3 unique visitors (not 5+2+2=9), and for URL-b I'd have 2 unique visitors (not 8). As a second pivot on the entire list, I'd want to count company=AOL, for example, once, not three times as it shows above. There are 15K rows of data for this one parent site and it sub-sites (and all docs that can be downloaded which have their own URLs) and the data is organized as shown above (grouped by unique URL). For the entire 15K rows, I'd want to list each occurence of a userID once, not each time the person connected and browsed. How can I do that?

    I need to do more study in combining data for these pivots. I never work with rows of data like this so I never have a need for pivots.

    Thnx,
    Deb

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting duplicates (Excel 2003)

    You seem to be talking about a different data structure than before, so I'm confused. Could you attach a small demo workbook with dummy data?

Posting Permissions

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