Multiple lookup

[JeffH]

I have a worksheet that list all employees by employer number. I have a
second worksheet whereby I would like to enter the employer number and have
all the employees associated with that employer number listed.

How best can I do this?

Vlookup will only provide one employee. There has got to be a simple way to
do this.

 

[Gary''s Student]

AutoFilter is the usual way to list all the rows associated with a match to a
given value on a given column.

 

[JeffH]

I realize autofilter is a way to view data in a worksheet. However, I think
my problem is a little different.

On worksheet 1, I want to input the employer number and have it
reference/list from worksheet 2 all the employees listed by employer number.
Vlookup doesn't work because there are multiple employees per employer
number.

I realize I could go to worksheet 2 and simply do an autofilter, but the
report is on worksheet 1.

I appreciate your help.

 

[Gary''s Student]

This is just an example that you can adapt to your specific needs. Say that
in Sheet2 the numbers are in column A and and the names are in column B.

In Sheet1 we put the required number in cell A1

In a standard module, put the following VBA code:

Sub xfr()
Dim rxfr As Range
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
v = s1.Range("A1").Value
n = s2.Cells(Rows.Count, 1).End(xlUp).Row
Application.EnableEvents = False
s1.Range("B:B").Clear
Set rxfr = Nothing
For i = 1 To n
If (s2.Cells(i, 1).Value = v) Then
If rxfr Is Nothing Then
Set rxfr = s2.Cells(i, 2)
Else
Set rxfr = Union(rxfr, s2.Cells(i, 2))
End If
End If
Next
rxfr.Copy s1.Range("B1")
Application.EnableEvents = True
End Sub



In the Sheet1 code area put the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Call xfr
End Sub

The the Sheet2 code area put the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:B")
If Intersect(Target, r) Is Nothing Then Exit Sub
Call xfr
End Sub

Now whenever you change the table in Sheet2 or the lookup value in Sheet1,
the lookup process will refresh.

 

[JeffH]

Thanks Gary, I appreciate your help. I've entered your code but I am getting
an error when it tries to copy.

"rxfr.Copy s1.Range("B1")" gets hung up and I'm not versed in VB code enough
to figure it out.

 

[JeffH]

Gary

Here is the code I have entered.

Note that the range in s1 ("Med UW Work Up") starts at A14, not A1
The data on s2 ("Census") starts at cell A2

Sub xfr()

Dim rxrf As Range
Set s1 = Sheets("Med UW Work Up")
Set s2 = Sheets("Census")
v = s1.Range("a14").Value
n = s2.Cells(Rows.Count, 2).End(xlUp).Row
Application.EnableEvents = False
s1.Range("B14:J66").Clear
Set rxfr = Nothing
For i = 14 To n
If (s2.Cells(i, 1).Value = v) Then
If rxfr Is Nothing Then
Set rxfr = s2.Cells(i, 2)
Else
Set rxfr = Union(rxfr, s2.Cells(i, 2))
End If
End If
Next
rxfr.Copy s1.Range("B1") I would think this should be "B14" because that is
where the range starts. However it errored out even when at "B14".

End Sub

 

[Gary''s Student]

Thanks for your patience. I will renew the attack tomorrow when I get to a
computer with Excel.

 

[Dave Peterson]

How do you want to display those employees? In one cell, but separated by
commas? In separate cells???

If you want a single cell:

http://groups.google.co.uk/group/mi...author:peterson&rnum=1&hl=en#db28f1ba868980a8

or

http://snipurl.com/24yfb (first post in the thread)




If you wanted separate cells (horizontal or veritical), take a look here:

http://groups.google.co.uk/group/mi...roup:*excel*+author:peterson#b5467b485b0a4f4d

or

http://snipurl.com/24yf3

 

[Max]

Think this little formulas play could also deliver what you want ..

Assume source Employer numbers & Employees in Sheet1,
cols A & B, data from row2 down

In Sheet2,
Assume A1 will be where you input the Employer #, eg: 111
Put in B1: =IF($A$1="","",IF(Sheet2!A2=$A$1,ROWS($1:1),""))
Put in C1: =IF(ROW()>COUNT(B:B),"",INDEX(Sheet2!B:B,SMALL(B:B,ROW())+1))
Select B1:C1, copy down to cover the max expected extent of data in Sheet1,
eg down to say C300. Minimize/hide away col B. Col C will return all the
employees for the employer # input in A1, neatly bunched at the top.