Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFilter Excel 2000 (Excel 2000)

    I have created a list of parking tickets in Excel 2000. I have about 1000 (records) tickets. I'm using the AutoFilter feature to view different records. Here is my problem. I want to find out all the users that have more than one parking ticket. Is there a way?? Some of the fields I have are First Name, Last Name, License Plate number, make of car. Help??

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Excel 2000 (Excel 2000)

    You have at least a couple of options.

    1. Add 2 extra columns. The 1st will be an "Index" which will be first & last name bolted together. Sort your data on this column. The 2nd column call "Count" (we'll assume "Index" is in column "D" & your header is in "D1"). Put the following formula in "E2"
    =IF(D2=D3,"","DUPLICATE")
    Copy this to the bottom of your data & select DUPLICATE from the filter.

    2. Create a pivot table & count the INDEX column.

    If you need any examples, let me know.

    No doubt there are other ways to do this including a bit of VBA

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Excel 2000 (Excel 2000)

    I think the Advanced Filter i sprobably more suited to your requirements. With it you can set a criteria range and set the criteria for any or all of your fields. Just because it is called Advanced does not mean it is difficult to use. Check the on line help and if you still have problems post back.

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Excel 2000 (Excel 2000)

    I would make a new column and concatenate the first and last names together (If Last Name is in cell A2 and First Name is in cell B2, in a blank cell type =A2&B2 and title the column Full Name for the sake of this example). Then I would use the subtotal function, at each change in Full Name, use function Count. You should then be able to sort the list in ascending or descending order to see who has how many tickets. You may have to turn off Autofilter to do this.

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter Excel 2000 (Excel 2000)

    This little example might help. Uses COUNTIF to give you a count of tickets for each entry by counting all matching entries above the current entry.
    The count depends on its order in the list so you can use BAN's trick to identify the last entry providing the list is sorted on the index.
    Now you can look at any number of parking tickets against any particular person (or filter for all those with more than n tickets etc. etc.)

    Cheers,
    Paul
    Attached Files Attached Files

Posting Permissions

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