Closed Thread
Results 1 to 11 of 11

Return a list of unique matches

  1. #1
    Registered User
    Join Date
    11-27-2006
    Posts
    9

    Return a list of unique matches

    I need to return all the unique matches from a set of data.
    For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.

    Example of data set I need to lookup in:
    prod 1 venue1
    prod 2 venue 2
    prod 1 venue 3
    prod 1 venue 10
    prod 2 venue 6
    I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
    It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....

    Would be grateful for any help!

    Nicky
    (South Africa)

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi Nicky,

    The easiest way out is from the menu Data Filter AutoFilter ...
    then pick the criteria you need to look at ...

    HTH
    Carim

  3. #3
    Registered User
    Join Date
    11-17-2006
    Posts
    34
    Alternatively you can highlight the data you want to filter on, go to Data, Filter, Advanced Filter, select check the Unique records only box and OK.
    This should filter and remove any dublicates and only leaves you with unique records.

    ResulG

  4. #4
    Registered User
    Join Date
    11-27-2006
    Posts
    9
    Unfortunately I have looked into Filter and Advanced Filter in quite some detail. However I need to populate a listbox with the appropriate list of venues each time the user selects a product. And thus on a seperate sheet I need to have some kind of vlookup type of formula that returns the name of each venue if a chosen product is selected....

  5. #5
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Nicky ...

    You are demanding ...lol

    Attached is a sample worksheet to return more than one value ...

    HTH
    Carim
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-27-2006
    Posts
    9
    Thanks, that definitely looks like what I need!
    Will now spend some time going through your formula so that I can apply it, looks quite involved!!

  7. #7
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    It is only because you are from South Africa... !!!

    Thanks for the feedback

    Carim

  8. #8
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    NickySA,

    To add to Carim's solution,

    Since different product codes may result in a varying number of venues you can avoid seeing blanks in your list by making the list in your listbox dynamic by doing the following.

    In Carim's workbook cell H2 input the formula below. Extend the range of the formula beyond the max number of venues that could result for a product. I used B15.

    =COUNTA(B2:B15)-COUNTBLANK(B2:B15)

    Define cell H2 as ListLen (stands for list length). Insert>Name>Define. In "Names in Workbook" type ListLen and in the refers to type =Vlookup!$H$2.

    Define the following formula as List. Insert>Name>Define

    Refers to:
    =OFFSET(Vlookup!$B$1,(COUNTA(Vlookup!$B$1:$B$15)-COUNTBLANK(Vlookup!$B$1:$B$15))-1,0,-MIN(ListLen,COUNTA(Vlookup!$B$1:$B$15)-1),1)

    Then in the ListFillRange property of your list box just type in "List". This will dynamically update your listbox fill range from product to product.

    HTH

    Steve

  9. #9
    Registered User
    Join Date
    11-27-2006
    Posts
    9
    Thanks, that sounds very useful! I will go through it today and try it out.

  10. #10
    Registered User
    Join Date
    01-23-2007
    Posts
    2

    The attached does not seem to work if data is in a different file

    I need exactly what the attached file in Carims reply is doing. But if the data is in a different file not in the same file as the lookup it does not seem to work. Can anyone please help me out with this? Thanks.

  11. #11
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    I assume you mean you are working with separate workbooks ...
    With different workbooks, there is an added question to be addressed :
    are they open or closed ...?
    With open workbooks, there should be no difficulty ...
    With closed workbooks, the solution is indeed more complicated ...
    HTH
    Carim


    Top Excel Links

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