Macro to Search in list for matches

[mrr2]

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]

Hi mrr2,

You can do this simply without a macro - just put a filter across C1 and D1 and filter by State (i.e Col. D).

HTH

Robert

[mrr2]

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)

Please Login or Register  to view this content.

[AB33]

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

[mrr2]

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:

Please Login or Register  to view this content.

[AB33]

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.

Please Login or Register  to view this content.

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

[mrr2]

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

[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.

[mrr2]

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!

[AB33]

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

[mrr2]

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

[AB33]

Amended code.

[mrr2]

ROCK ON! this made my day!