Solved

Simple way to return multiple look up values?

Posted on 2011-03-16
8
212 Views
Last Modified: 2012-05-11

The work flow:

The user selects the company using a drop down and i want to display a list of products made by that company.
- i vlookup the company index based on the company thats selected
- i have a value lets say "2" in a cell
- i want to search the sheet below and return all products with the company index of 2.

The sheet follows the pattern below with 100's of rows. A simple formula is what i'm looking for.

Company Index | Product Index | Company Name | Product Name | Sales data
0
Comment
Question by:sanjangeorge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 35148785
A simple formula in a cell won't return multiple rows of data. Are you trying to return a concatenated list in a string variable or cell value, or are you trying to show all the rows that match the company index? If the latter, you could use a macro to trigger an autofilter on the page to filter by company index, but a cell formula can't do that. If the former, you'll still need a macro since there's no good way to run a custom loop on a set of cells with one formula.
0
 

Author Comment

by:sanjangeorge
ID: 35148926
I see, what would be the simplest VBA approach then?
0
 
LVL 19

Expert Comment

by:akoster
ID: 35148927
If you only want to display these products, why not use autofiltering ?

when you have a setup as above, select the column headers and activate filtering.
When a user then filters on a single company, or a combination of companies, only those will be visible.

in this case it is not necessary to use  formula's
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sanjangeorge
ID: 35148963
its not the way i'd like to display the data.
0
 
LVL 19

Expert Comment

by:akoster
ID: 35148997
or in a macro it would look like
Sub filter(index)
'-- assuming A column for company index
'--          B column for product index
'--          C column for company name
'--          D column for product name
'--          E column for sales data

    For Each Row In UsedRange.Rows
        If Row.Cells(1) = index Then
            '[..] do something with the data, eg.
            Debug.Print "Company " & Row.Cells(3) & " sells product " & Row.Cells(4) & " for " & Row.Cells(5)
        End If
    Next Row

End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:telyni19
ID: 35149032
You didn't really answer the question of which approach you wanted, except in reply to akoster where you said you didn't want to display the data using filtering, so I'm guessing you want to concatenate the information?

So you have a drop-down for the company choice. The technique I suggest is that when the value of the drop-down changes, the code determines which company index to use, searches the data for all produce names matching the company index, and concatenates them into a text string to either display on the page somewhere or show to the user.

I will be happy to construct a sample file that does this and attach it here for you. If you have an example file with a few rows of data that you could share, that would be better, as I could add on to you existing structure and layout rather than making my own. In particular, if you already have a drop-down set up, you'll want the code to respond to that drop-down, so it will matter how you have the drop-down set up (Excel object, data validation, etc).
0
 

Author Comment

by:sanjangeorge
ID: 35149135
telyni19: could you set this file up and show me what you think the best practices would be.

akoster: thanks for this i'll have a look at it now
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 35150087
Please see the attached file and code. The drop-down is controlled through Data Validation using a named range for the company list. With the named range, the company list could even be on a separate sheet to keep it out of the way. I've got it all on the same page so you can see what the relationships are.

The Worksheet_Change event tests to see whether the changed cell is the drop-down cell; if not, it does nothing, but if so, it runs a script to find all products that match the company index. Cell H2 has a hidden formula (text in white) to use VLookup to return the index from the selected name. Cell H1 has a hidden formula to count the company names in the list in case you wanted to set up some sort of dynamic reference for the company list or do something else with the number of companies.

Instead of hard-coding the references to these cells with useful values, as well as which cell is the drop-down and which cell is the place to put the concatenated result, I made each of those cells a named range also and used the names in the code. If you want to rearrange the layout, no problem, just make sure the names point to the right cells and the code won't need to change.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCIndex As Integer
Dim intRow As Integer
Dim strValues As String
Dim rngVal As Range
Dim strAddress As String

If Target.Address = ActiveSheet.Range("CompanyChoice").Address Then
    strValues = ""
    intCIndex = ActiveSheet.Range("SelIndex").Value
    Set rngVal = ActiveSheet.Range("A:A").Find(What:=intCIndex, LookIn:=xlValues, After:=ActiveSheet.Range("A1"), LookAt:=xlWhole, SearchOrder:=xlByColumns)
    If Not rngVal Is Nothing Then
        strAddress = rngVal.Address
        Do
            If Not strValues = "" Then strValues = strValues & ", "
            strValues = strValues & rngVal.Offset(0, 3).Value
            Set rngVal = ActiveSheet.Range("A:A").FindNext(rngVal)
        Loop While Not rngVal Is Nothing And rngVal.Address <> strAddress
    End If
    ActiveSheet.Range("ProductList").Value = strValues
End If
End Sub

Open in new window

MultipleProductsExample.xls
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question