Closed Thread
Results 1 to 17 of 17

Find all instances of a selected item and return all results

  1. #1
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Find all instances of a selected item and return all results

    Basically what the title reads.

    I would like to use Vlookup for all the items found in a dropdown menu. I can only return the same value.
    Vlookup may not be the function for this, I just don't know what else to use.

    Please look at the attached workbook. I only want to use formulas for this.

    Thank you for your time
    Attached Files Attached Files
    Last edited by NewGuy OnBlock; 01-11-2011 at 10:24 PM.

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

    Re: Find all instances of a selected item and return all results

    Hi NewGuy OnBlock and welcome to the forum.

    You need to make your list a table or add auto filter dropdowns. Then you will click on the Item dropdown and select the one you want. After doing this only rows with that selected item will show.

    see the attached and dropdown the item header.

    Is that what you wanted? No code and no formulas either!!
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thaks Marvin,
    I am aware of autofilter, but wish to get the information onto the other sheet using formulas.

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

    Re: Find all instances of a selected item and return all results

    How about Advanced Filters to a second sheet? You may need to do a named range of your data to make it easier.

  5. #5
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    I used advanced filter to get the unique items for the data validation, are you saying there is a way to use advanced filter in a formula so that each instance will be returned to the second sheet?

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

    Re: Find all instances of a selected item and return all results

    You said formulas only. It is very easy with a little code (if you can code). Without code you type the value you want in the criteria area and run the Advanced Filter again.

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

    Re: Find all instances of a selected item and return all results

    Ok look at this file.

    I created an Advanced Filter on the sheet and did a very quick macro to copy the value you double click on and then advance filter it. Easy Peasy.

    See if this works for you. Double click on any of the items to see filtered area change.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thanks Marvin,
    I know vba can easily be used for this, just loop through the column and when an item matches, return the item beside it.
    I want the user to be able to open a workbook that is macro free, select from the drop down menu and the results will appear where the formulas are.

    BTW, your example using VBA is reversed from what I am looking for.
    Last edited by NewGuy OnBlock; 01-09-2011 at 08:25 PM.

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

    Re: Find all instances of a selected item and return all results

    I think you're going to need some code to do what you want.

    Maybe one of the smart forum guru's can help with this one.

  10. #10
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thanks Marvin,
    I will wait and see what results I can get today.
    There must be a way to get all instances using a formula.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Find all instances of a selected item and return all results

    Do you want to "look up" companies or items?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Find all instances of a selected item and return all results

    Try this workbook.

    Select the Company from the drop-down in A2 (yellow)

    The helper column can be hidden, use the group button in this example ( the +/- button below the formula bar)

    The formula in B3 is the key to this sheet
    Please Login or Register  to view this content.
    This is an array formula and must be confirmed with Ctrl+Shift+Enter , not just enter.

    The named ranges I have used are all dynamic, so no need to change as your data range grows.

    I have added a List of "companies" on a separate sheet, add to / subtract from this as new customers become available.
    Just remember to keep the list continuous, i.e. no blanks.
    This table could be on Sheets("Data").

    Hope this helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  13. #13
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thanks ChemistB,

    If you click on the data validation in A2, all the items matching that selection dill display. Like Vlookup such as in the sample workbook, but with all the instances appearing.
    Last edited by NewGuy OnBlock; 01-11-2011 at 09:29 PM.

  14. #14
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thanks Marcol,
    Your solution certainly does work with your example.
    The number column in my sample workbook is not a count of items but will be different numbers. I just dragged numbers down, not knowing that you would use that column as part of your solution.

  15. #15
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Using your example and altering the formula
    =IF(Data!C3=$B$1,ROW(Data!A3),"")
    will bring the row number
    Can index match then be used to to get the result with the row number from column D?

  16. #16
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Ahh possible
    =IF(B3="","",INDIRECT("Data!D"&B3))
    Last edited by NewGuy OnBlock; 01-11-2011 at 09:51 PM.

  17. #17
    Registered User
    Join Date
    01-09-2011
    Location
    Gabrieola
    MS-Off Ver
    2003,2007
    Posts
    42

    Re: Find all instances of a selected item and return all results

    Thanks,

    My sample workbook shows that I wanted to find all the criteria from column C and get the results from column D.

    With your help this has been accomplished.

Closed 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