Full Version: VLOOKUP - Find one match only
bakersburg9
I'm not sure if VLOOKUP is even the answer to this challenge, but I have sales data by region, one worksheet for region - but I want to show the underlying store numbers. I have a worksheet that shows the storeNum and the associated regionNum.

Here's what my worksheet that serves as the source for the VLOOKUP looks like:

StoreNum....StoreName...RegionNum
....22.........Austin............1
....39.........Dallas............3
....22.........Austin............1
....22.........Austin............1
....39.........Dallas............3

On the worksheet for Region#3, the Region number is in cell A1 - I would like to put a formula in Cell J21 that looks up that number, finds a match ( Store 39/Dallas ) , shows that value in cell J21, then in the cell below it, cell J22, look for another match, but ignore the match it already found and returned in cell J21, and put Store 44 / Addison in cell J22. If there are no more matches that don't already exist in the cells above, just return a blank.

Is this even possible ?
KingMartin
Hi Steve,

I think you should rather automate autofilter to dump the data from the master into the Region sheet.

Because you want to concatenate two values into one cell (Why, actually???), you can't copy-paste the data directly. You'll need to fire up autofilter on the master data filtering the region in question and then loop through the visible rows and concatenate the values into J21, J22, etc of the respective sheet.

Martin
bakersburg9
I don't need to concatenate two values into one cell - just wrote it that way so it would be easier to read in UA
KingMartin
QUOTE
just wrote it that way so it would be easier to read in UA

QUOTE
and put Store 44 / Addison in cell J22

Well, well.. it confused me completely

OK, do you need a helping hand with the code? If so, would you have a small example of your workbook with some data and 1-2 regions?

Martin
bakersburg9
I threw something together real quick - thank's for your help
KingMartin
Hi again Steve,

based on your example, try the following:

Make the regional sheet active and run this code:

QUOTE
Sub GetRegionData()
Dim rg As Range
With Sheets("qryRegionStore")
If ActiveSheet.Name = .Name Then Exit Sub
Set rg = .Range("A1", .Cells(.Rows.Count, "A").End(3)).Resize(, 4)
rg.AutoFilter 1, ActiveSheet.[A2].Value
ActiveSheet.[D:E].ClearContents
rg.Offset(, 2).Resize(, 2).Copy ActiveSheet.[D1]
.AutoFilterMode = False
End With
End Sub

If it works for you, assign a hotkey to this macro and cycle through the sheets.

Of course, all the sheets could be generated automatically, but I don't know how complex the setup is...

HTH,

Martin