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
....44.........Addison .........3
....27.........Pasadena........1
....44.........Addison .........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 ?
Full Version: VLOOKUP - Find one match only
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
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
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
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
I threw something together real quick - thank's for your help
Hi again Steve,
based on your example, try the following:
Make the regional sheet active and run this code:
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
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
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.