matching and returning results

  • Thread starter Thread starter ob_canada
  • Start date Start date
O

ob_canada

Hello,
I need assistance in creating a formula/string to return all location
results (into individiual cells) based on search criteria. Please see
example.

Location Item Item Item
A 100 400
B 400
C 400 100
D 300
E 200 100
F 200
G 100
H 100 300
I 200 300 100
Search Results
100 A C E G H I
200 E F I
300 D H I
400 A B C

Thank you in advance!
:confused:
 
If I understand correctly what you want, the following macro will do that
for you. I assumed your data starts with A1 and goes down and to the right,
with location in Column A. The search criteria (4 cells in a column with
100, 200, 300, and 400 in them) is in B11:B14. Watch out for line wrapping
of the code in this message. View this in full screen. If you wish, send
me an email with a valid email address for you and I'll send you the small
file I made up for this. Mention in your message that the file is named "Oh
Canada.xls". My email address is (e-mail address removed). Remove the "nop"
from this address. HTH Otto
Sub SearchData()
Dim RngColA As Range
Dim i As Range
Dim RngRow As Range
Dim SearchCr As Range
Dim j As Range
Set SearchCr = Range("B11:B14")
Set RngColA = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each i In SearchCr
For Each j In RngColA
Set RngRow = Range(j.Offset(, 1), Cells(j.Row, Columns.Count))
If Not RngRow.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing
Then _
Cells(i.Row, Columns.Count).End(xlToLeft).Offset(, 1) =
j.Value
Next j
Next i
End Sub
 
Back
Top