Return Multiple Rows From Lookup & Search

[rhodie5803]

Hello,

I was wondering if anyone may be able to help.
I am required to produce a list of contractors names and personal details such as email addresses and postal addresses on a monthly basis. Currently this list contains anywhere between 1-2000 people. To create the required list I am have to sort through various other employee lists and filter out the people I require. Each employee list may contain as many as 1000 or so employees. (Currently I have around 17 employee lists...)
Previously I was able to use a VLOOKUP "cheat sheet" that I created to assist in filtering out the people I required etc. However my task has recently changed and I am now required to provide both primary, secondary and any additional contact details for each member of my list. I can get the source data without any hassles but where I am having trouble is filtering out only those members I need. I noticed that VLOOKUP only provides the first row of data for each individual I searched for. I believe there are ways to provide the either the first or last row but ideally I require all rows of data for each individual I am searching for.
I have included a rough example of the sort of spreadsheet I am hoping for. Basically it consists of three areas 1 tab for the employee's I need, another for my source data and the third as a results tab. I find it easier to have my results on a seperate tab so I can copy/paste all etc.
If anyone would be able to help create this workbook for me I would be willing to pay $10 for a working copy. If this seems a little low for the task let me know and we can negotiate another price.

Thanking you

[Dave Hawley]

If you fill the blanks between your names and employee id you can base a PivotTable off a Dynamic Named Range and place the employee id as the report filter. See attached example.

If filling blanks is a big task, see Fill Blanks With Cell Above

[rbrhodes]

Hey Daves' on it, but I'll have a look as well...

[rhodie5803]

Hi Guys,

Thanks for replying.

Dave, I have never really played with pivot tables but the only problem I could see with your example is that I would need to manually select each individual that I was looking for which in some cases can easliy be in the hundreds of people.

In my proper workbook I managed to add the autofill plugin which works brilliantly. (I can't remember who created it here but I will post credit when I find out who. It is an essential little tool, thanks

The reason I was hoping for a workbook with different tabs is so as I get sent a list of names etc I can just dump in the employee ID's in one tab and the source data in the other and then grab the results in the third tab.

Basically I'm trying to streamline the whole process.

I'll keep playing with your pivot table and I will try it at work tomorrow to see what I can do with it.

Thanks for the help so far.

Cheers

Rhodie

[Dave Hawley]

that I would need to manually select each individual that I was looking for which in some cases can easliy be in the hundreds of people.

If you have a list of names or id's the selection on multiple names/id's can be automated.

[rhodie5803]

Originally Posted by Dave Hawley

If you have a list of names or id's the selection on multiple names/id's can be automated.

Hi Dave,

Sorry if I am making this hard but how would I automate the selection of names or ID's?
Once again sorry about this but it's my first time with pivot tables

Hey!!.... who just said NOOB? I'll find who ever just said it... ha ha ha just kidding

Thanks again guys.

[Dave Hawley]

With this code

Sub ShowHideNames() 
    Dim pt As  PivotTable, pf As PivotField 
    Dim pi As PivotItem 
     
    Set pt = Sheet1.PivotTables("PivotTable1") 
    pt.ManualUpdate = True 
    Set pf = pt.PageFields(" Name") 
    Application. ScreenUpdating = False 
    With WorksheetFunction 
        For Each pi In pf.PivotItems 
            pi.Visible = .CountIf(Range("Names"), pi.Value) 
        Next 
    End With 
    pt.ManualUpdate = False 
    Application.ScreenUpdating = True 
End Sub 

Just make sure the PivotTable is called "PivotTable1" and you have a Named Range called "Names" with all the names you wish to show on the PivotTable.

See working example attached.

[rhodie5803]

Hi Dave,

Thanks for your help with this.
I can see that it works which is great, I will have to do a bit of study on pivot tables etc to get it to work how I need it.
But thanks again for your time and effort.
Please let me know how you would like to be paid for this.

Cheers

Rhodie

[Dave Hawley]

PayPal, please. Send money to paypal@ozgrid.com, thanks.