Macro to Search in list for matches

Hi All - I tried doing this yesterday but am doing a different spin on this today to hopefully get an answer...

I would appreciate it if someone could help me with a macro that would search for an inputted state in $h$2 and automatically output all the chains that are in that state in k:k


Please help!

Thank you

LookUp Macro Help 8.13.13.xlsx

Trebor76 - I am pressed to make this easy as possible for sales people to use i.e. inputting a state and getting a response...

would a variant of this Macro help? (found doing searches)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim PasteRow As Long
Dim CompanyList As Range

Application.EnableEvents = False

PasteRow = WorksheetFunction.Max(3, Range("D65536").End(xlUp).Row)

If Target.Address = "$B$2" Then
    Range("D3:E" & PasteRow).Clear
    PasteRow = 3

    For Each CompanyList In Sheet2.Range("ProductList")
        If CompanyList.Offset(WorksheetFunction.Match(Sheet1.Range("SelectedCompany"), Sheet2.Range("CompanyList"), 0), 0) <> "" Then
                Range("D" & PasteRow) = CompanyList.Value
                Range("E" & PasteRow) = _
                        CompanyList.Offset(WorksheetFunction.Match(Sheet1.Range("SelectedCompany"), Sheet2.Range("CompanyList"), 0), 0)
                PasteRow = PasteRow + 1
        End If
    Next
End If

Application.EnableEvents = True

End Sub

Attached is an event driven code. If you change the value of H2, the code will copy all rows related to H2.

Thanks AB33 thats kind of what I am looking for - I appreciate your help with this...

What I am looking for is if I enter a State, to get a all the stores chains (unique names only) that are in that state....

How can we modify the code you graciously wrote for that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$2" Then
    Call filterme
    End If
End Sub

stores chains (unique names only).
You need completely different code if you want to copy unique values only. Not filter, but you need advance filer.
So if I enter FL in H2, there is a single unique value ONLY.
I am probably missing something.
Change this line of the code(Main code) and see the outcome.

 If LR > 2 Then .Range("D2:D" & LR).Copy .Range("K2")
INTO

 If LR > 2 Then .Range("C2:D" & LR).Copy .Range("K2")

The code copies both the chain and state, but not unique. Which of the two columns need to be unique?

Sorry if I'm confusing you....

If I enter the State FL in H2, I would like to see all of the unique chains that we have in FL, if there are multiple Dollar Generals for instance, I would only need to know that Dollar General is there, so I would only need it to return Dollar General and any other unique values of stores that are in FL.

I sincerely hope that helps, let me know if not...

Thank you AB33

If do filter by FL, I get 5 (7- Eleven),3 (Arco) and so on. So, I suspect you just want to 1(7- Eleven) and 1 Acro.

Yes! you got it!
And, I hope that what you help me with, I will be able to translate that template to be able to enter a chain, and then getting all the unique states that chain is in
Thanks AB33!

Try the attached.
I am not sure if the unique values comes from column K or L, but can easily tweak it.

Fantastic, thanks so much AB33!

I was (cautiously, so as to not break your code) able to play around with it and also do the reverse, i.e. look up states based on a Store input...

This case is most definetly solved, and I will mark it as such. I have only one question, how can I can use that code if the data is on a different sheet, and the macro input/output is on the Summary sheet, like in the attatched file.

Again, I can thank you enough for your help AB33 - if you can help further then great, but nonetheless you were nothing short of awesome!

LookUp Macro Help 8.13.13 answer2.xlsm

Amended code.

ROCK ON! this made my day!