Solved

Simple way to return multiple look up values?

Posted on 2011-03-16
8
214 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:Arno Koster
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

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

Expert Comment

by:Arno Koster
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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‚Ķ

732 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