Solved

Simple way to return multiple look up values?

Posted on 2011-03-16
8
201 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
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
Comment Utility
I see, what would be the simplest VBA approach then?
0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
 

Author Comment

by:sanjangeorge
Comment Utility
its not the way i'd like to display the data.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now