Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter by data acros multiple columns (Excel 2002)

    In column A of my worksheet I have an inventory list (about 800 items) in alphabetical order. Columns B to F have other details about each item. From column G onwards each column represents one month and lists against each item in column A the name of borrower of that item where each of 15 users is allowed to borrow 4-6 items per month. I want to sort or filter so that I see all the items borrowed by only one particular user at a time across all the months.

    Can anyone help?

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

    Re: Filter by data acros multiple columns (Excel 2002)

    The layout of your table is not very handy for the type of filtering you want to do. I would use two tables:

    1. The inventory list (columns A through G, about 800 rows)

    2. A separate table with 3 columns:
    - Inventory item (corresponding to column A in the first table)
    - Name of borrower
    - Month (or date)
    If necessary, you can add columns with formulas to look up details about the inventory item from the first table.

    There can be multiple rows with the same inventory item in the second table, if the item has been borrowed more than once. You can use AutoFilter to select all rows for a specific borrower.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by data acros multiple columns (Excel 2002)

    Thanks. You confirmed my fears that the table as is at the moment wasn't created the most optimal way. I can see what you are suggesting and will try it out later.

    I appreciate your time.

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by data acros multiple columns (Excel 2002)

    Actually, you gave me a remarkably simple and quick solution for my needs.
    I used the original worksheet and copied columns A and B (which was enough to for my needs) to a new worksheet and then the name of borrower into a new column C and added the month name in column D. Only 90- 100 items are borrowed each month so I filtered the original worksheet after hiding non-relevant columns and copied columns A and B and one month at a time into new worksheet. So far the data base has been running for 7 months so that wasn't a lot of work. And it gives me what I need - a display of all items borrowed by each person over all the months.

    Many, many thanks.

Posting Permissions

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