Multiple Tab Lookup with multiple returns

[dkidroske]

I have a zipcode lookup tool which uses vlookup to get the names of territories, sales managers, sales directors, and time zones. It works off of the standard list of primary zipcodes (around 42k) plus a few that we know belong to current clients that are secondard zips.

Now I'm trying to do a client lookup. The problem is that the list is more than 89000 rows and I have to use Excel 2003 to allow the users access to the tool. I've divided the data into three alphabetic tabs.

I'd like to do a multi-tab lookup and have the results (1 or many) dumped to a spreadsheet box.

Any way to do this outside of vba? Anyone have a good vba solution?

 

[Yard]

How is the data split up into 3 tabs? A-H, I-S, T-Z or something like that?

And what is it that you are looking up?

 

[dkidroske]

Yes, it' a-g, h-p, q-z. a-g includes all companies whose names start with a number.

I'm looking to do a one tab addition with a lookup tool that can use a word from the name of the company and list all of the companies on the three tabs that have that word in them. Outputs would include keyfield, company name, city, state and zipcode.

 

[Yard]

So:

Sheet A-G lists companies from 1st Electric to Grandad's Store along with their details in adjacent columns
Sheet H-P lists companies from Harry's Store to Pringles along with their details in adjacent columns
etc
etc

and either:

you want to enter "Harry's Store" in a cell and have the relevant details for that company brought up in adjacent cells

OR

you want to enter the word "Store" in a cell and have "Grandad's Store", "Harry's Store" brought up in adjacent cells.

OR

is it something else I've not understood?

 

[dkidroske]

you got it.

 

[Yard]

LOL

It was a question!

EITHER

you want to enter "Harry's Store" in a cell and have the relevant details for that company brought up in adjacent cells

OR

you want to enter the word "Store" in a cell and have "Grandad's Store", "Harry's Store" brought up in adjacent cells.

OR

is it something else I've not understood?





To get decent help YOU need to specify what you want to see and where you want to see it.

e.g., you say:

I want to enter "Harry's Store" in cell A1 and have details for that store brought up in cells B1:D1

OR

I want to enter the word "Store" in cell A1 and have "Grandad's Store" brought up in A2 with its details in B2:D2, then "Harry's Store" brought up in A3 with its details in B3:D3............

Get it?
<!-- / message --><!-- sig -->

 

[dkidroske]

Ideally it would be both, but as stated originally, just one word. So "store" is the right response.

 

[Yard]

Right, try this.

Right click the tab of your Lookup sheet, click View Code and then paste this into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLook As Range, rngCopy As Range, ws As Worksheet
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
If Target.Address(0, 0) <> "A1" Then Exit Sub

Application.ScreenUpdating = False
Me.Range(Target.Offset(1, 0), Target.Offset(1, 0).End(xlDown).End(xlToRight)).ClearContents
For Each ws In Sheets(Array("A-G", "H-P", "Q-Z"))
    Set rngLook = ws.UsedRange.Columns(1)
        With rngLook
            Set LastCell = .Cells(.Cells.Count)
        End With
        Set FoundCell = rngLook.Find(what:=Target.Value, after:=LastCell)
        
        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
        End If
        Do Until FoundCell Is Nothing
            Set rngCopy = FoundCell.Resize(, FoundCell.End(xlToRight).Column - FoundCell.Column + 1)
            rngCopy.Copy
            Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
            Set FoundCell = rngLook.FindNext(after:=FoundCell)
            If FoundCell.Address = FirstAddr Then
                Exit Do
            End If
        Loop
        
Next ws
    
Application.ScreenUpdating = True
End Sub

Whenever you change the value in A1 it should look through each of the sheets specified and return whatever details are adjacent to the matching values.

Works fine on my example data, just depends how yours is laid out.

HTH

 

[dkidroske]

Thanks, for the help!!!!

I'll try this later today (crashing on other projects for this am and early pm). Once I've tried it, I'll let you know how it worked out.