+ Reply to Thread
Results 1 to 4 of 4

Formula to return multiple answers matching criteria

  1. #1
    Registered User
    Join Date
    08-03-2009
    Location
    NY, NY
    MS-Off Ver
    Excel 2003
    Posts
    60

    Formula to return multiple answers matching criteria

    On the attached spreadsheet, I have a list of vehicles and dates, with the vehicles marked on each day that it was used.

    It looks like this:

    ................Truck 1.......Truck 2.......Truck 3
    1/1/10...........1................1.......................
    1/2/10...........1..................................1.....
    1/3/10...........1................1................1.....

    On the second tab, I was hoping that I could have the vehicles listed across the top, and then, underneath, display a list of all of the dates that that vehicle was used. Similar to this:

    Truck 1..........Truck 2..........Truck 3
    1/1/10............1/1/10............1/2/10
    1/2/10............1/3/10............1/3/10
    1/3/10...........................................

    I will then use "countdiff" to find the total amount of unique values.

    Thanks in advance for the help,
    - Stu
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Formula to return multiple answers matching criteria

    Try this array formula in Summary!J4:

    =IF(ROWS($A$1:$A1)>Data!D$2, "", INDEX(Data!$B$1:$B$37, SMALL(IF(Data!D$1:D$37>0,ROW(D$1:D$37), ""), ROWS($A$1:$A3))))

    ...confirmed by pressing CTRL-SHIFT-ENTER. The date 12/03/09 should appear.

    Now copy that cell downward and to the right...
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Formula to return multiple answers matching criteria

    Hi TakeTwo,

    Have you ever considered a pivot table for you data? See attached.
    It looks like you rent tools and trucks out. You could have a drop down validation for all the products you rent which would allow easy entry. You would then enter three things for each transaction: Date, Item, days rented. If an item wasn't rented for a day, no transaction is entered. A pivot table then would easily summarize all information. See attached.
    Attached Files Attached Files
    Last edited by MarvinP; 12-06-2010 at 12:30 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Formula to return multiple answers matching criteria

    Sorry, I see you are on 2003 so you need the .xls example attached.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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