Best Way to count records in spreadsheet?

Discussion in 'OT Technology' started by cassetti, Oct 25, 2005.

  1. cassetti

    cassetti New Member

    Joined:
    Nov 30, 2003
    Messages:
    252
    Likes Received:
    0
    Location:
    delaware
    Ok - here's the problem

    I have a crapload of records at work - think of it as like say items we ship out to customers - each row in this excel file is a differant record - what I need to do - is count up all records based on the customer name.

    I know i can use "subtotals" on excel - but there is two problems with the subtotal function:

    When it does subtotals - i can't easily copy the whole records or anything.

    i don't know how to explain it - but subtotals isn't doing what i want - anyone know of any add-in or organization program that can group data in a spreadsheet together.

    i would love to find a freeware one that has fuzzy logic on it - to handle typo's since i know they happen frequently

    Thanx for any help you can offer!
     
  2. cmsurfer

    cmsurfer ºllllllº

    Joined:
    Jun 6, 2003
    Messages:
    5,079
    Likes Received:
    0
    Location:
    NJ
    Can you sort the columns by customer name and then count them that way?

    When I have to count similar things in Excel at work, I create a new column and put a 1 for each one I want to count then sum that column.
     
  3. cassetti

    cassetti New Member

    Joined:
    Nov 30, 2003
    Messages:
    252
    Likes Received:
    0
    Location:
    delaware
    Not exactly, i can't do it manually - there are roughly 40,000 lines here
     
  4. daviid

    daviid cell tower tech

    Joined:
    Nov 7, 2003
    Messages:
    1,628
    Likes Received:
    0
    Location:
    Concord, NC
    make a new column and put 1. then drag the corner of the cell down till u get to the last record. then sum the column
     
  5. cassetti

    cassetti New Member

    Joined:
    Nov 30, 2003
    Messages:
    252
    Likes Received:
    0
    Location:
    delaware
    i want to count the various ones

    like there are 40,000 records, but there might be say 10 or 20 for the same guy, i want to count how many per guy.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    This would be cake in SQL/Access.

    Use the right tool for the job, make your life easier
     
  7. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas
    There's not going to be a way to do this outside of VBA and even then I'm not sure it's worth the effort.

    Here's a dirty way to do it

    Run a filter for only unique customer name data and save that to a new sheet. Sort that sheet by customer name. Take that list over to the original sheet and type out a quick countif statement for each range and name (assuming there aren't thousands)
     
  8. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    Count it manually! Hire the homeless guy downstairs for an afternoon.

    1, *press down*
    2, *press down*
    3, *press down*
    ...
    20,000 *press down*
    ...
    40,000....
     
  9. RyanL

    RyanL OT Supporter

    Joined:
    Nov 30, 2004
    Messages:
    4,584
    Likes Received:
    0
    Location:
    St. Paul, MN
    He might not be freeware, but if you buy him a fifth at least you'll get fuzzy logic :hsughno:
     
  10. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
  11. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    :werd: There is no reason why a spreadsheet should have 40,000 records in it.
     
  12. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas

    There's nothing at that website that can do this. Counting unique values wont allow him to sum the number of sales to each unique customer and tagging duplicates wont help since he can't tag each duplicate with a unique value
     
  13. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
    What are you talking about:

    The info on that website will allow him to do exactly what he seems to be asking for.

    edit: and here is how since I guess it's not obvious:

    1. Use the info from the site to to extract a unique list of customers to another column (pref. on a separate worksheet)
    2. Use that list and a sum(countif) formula to count the instances of each name in the order list for each unique customer in the list created in step 1. Info on Countif is also on that website, just check the index if you/he doesn't know how to use it.
     
    Last edited: Oct 26, 2005
  14. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas

    No, there are 40,000 records of sales but say 100 customers. He needs to total how many sales have gone to each customer (that's my understanding).

    That website will not show him how to do that.

    The array formula for dup entries on that site (and any other method of simply tagging dups including conditional formatting) will not work because it will simply display "duplicate" next to each entry that has a duplicate customer name ... however all that will do is put a bunch of "duplicates" down 40,000 rows of data, and since all that column will display is "duplicate" and not a unique value for each unique customer it will bring him no closer to being able to count the number of records for each individual customer.

    Counting the number of unique entries in the list will do no good because it will only tell him how many unique customers he has and not how many orders have been made by those customers.
     
  15. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
    re-read the edit.
     
  16. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas

    Holy shit, all you just did was tell him how to do the same thing I proposed yesterday ... but with the added headache of using a formula to get a list of unique customer names instead of just running a simple filter. And that still doesn't solve his problem since there could easily be 1000 unique customers and who really wants to spend the time writing out a countif for 1000 different customer names.
     
  17. cmsurfer

    cmsurfer ºllllllº

    Joined:
    Jun 6, 2003
    Messages:
    5,079
    Likes Received:
    0
    Location:
    NJ
    Pretty much what I said...

    You didn't answer my question... Can you sort the spreadsheet by customer name?
     
  18. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
    Only a total dumb-ass would. That's why you extract the unique list. Duh. Create the first one with a reference to the cell with the first unique name in (instead of explicitly putting the customer name in) then copy, highlight the rest of the range, and paste. Excel will automatically change the formula for each row.
     
  19. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas

    As far as I know Excel will only change the cell reference (assuming it's not an absolute) when you copy the formula down a row or column, not the data in the cell itself. Maybe that's just my own excel shortcoming though :dunno:
     
  20. bradj

    bradj give him the stick...DON'T GIVE HIM THE STICK!!

    Joined:
    Sep 5, 2003
    Messages:
    2,718
    Likes Received:
    0
    Location:
    Auburn
    Pivot Table.
     
  21. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
    Exactly. That's why you reference the name in the unique column cell to the left (for instance). So on row 1 you are comparing the name in cell A1 to the orders list, then copy the formula to row 2, and excel will change the reference so it is now comparing the name in cell A2 to the order list, etc. That way you only write one formula and copy it down the column, with explicit ($A$1 for instance) references to the order range and non-explicit (A1 for example) references to the cell in the unique list.

    It does work. I had to do this a log file once. I converted it to a CSV, imported it into excel, extracted the unique events and counted and sorted them by how often they occurred. Not elegant but it was fast and easy since I only needed the info once.
     
  22. Moistfly

    Moistfly Anal Bum Cover

    Joined:
    Oct 19, 2001
    Messages:
    12,434
    Likes Received:
    0
    Location:
    Texas

    Yup you're right. If you do this method just make sure that as you copy the countif equation down you use explicit values ($A$1:$A$4000) for your range.
     
  23. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    40,000 lines in an excel spreadsheet... Why not merge this onto a database? You're going to outgrow a single Excel worksheet at around 65,000 lines.
     
  24. EvilSS

    EvilSS New Member

    Joined:
    Jun 11, 2003
    Messages:
    5,104
    Likes Received:
    0
    Location:
    STL
    BTW most of these ideas all revolve around the idea that the duplicate names are exactly the same. For instance, John Smith on all orders, not John Smith on one, Jon Smith on another, and john smith on yet another one. If they don't match exactly, that's going to make it extremely difficult to do.
     

Share This Page