Array function to look up multiple values

[kbon115]

Hello,

I'm currently working on getting this array function to work for me. Basically, I need it to pull specific items from a list based on a criteria. For instance, I have an excel report that i run weekly that lists different vendors' credits on account. These vendors are split among our sales people by state. So instead of copying and pasting each line (company A, California, $$$credit // company B, California, $$$credit // company c, Alabama, $$$credit) into each salesperson's excel worksheet, I'm trying to get it so I can pull them all at once. So for Alabama, California, and 4 others states that Bob controls, I can pull all those and paste it onto its own worksheet for Bob. That's the idea.

Right now, what I have works, but only for one state at a time. I'm using

Please Login or Register  to view this content.

In the bold is the specific cells on the report. I keep them $locked.

But it'd make my life way easier if I can include more than one state at a time. Otherwise, I'm copying and pasting the formula and changing it around for both the state and the column number (I already have to do the column changes at the very end of the formula to get 1-9 across the board).

I've tried nesting IF formulas into it, I've tried choosing multiple cells for the IF criteria (for instance, IF(REPORT STATE COLUMN=$A$1:$A:$5,etc.)), but I'm just not having any luck with any of those. I'll either get a statement saying I have too many arguments or too few arguments. Does anyone have any insight on what I can do here? I'm worried that I might be entering VBA territory, which I have extremely limited knowledge of.

Thank you.

[kbon115]

I can provide an attachment if that'll help anyone's understanding of what I'm trying to say... Does anyone have any suggestion at all?

[Alf]

Have you tried sorting your data using autofilter?

I can provide an attachment

That would be a good idea as the way your data is set up will have an impact on how this problem could be solved.

Alf

[kbon115]

Here's an example of what I'm going for

[Alf]

The only way I could solve this was using a macro.

There are a number of things to consider, two sheet names are "hard coded" in the macro, not good.

Then there is the problem with the state names in the first sheet for instance "California" but in the second sheet you have both "California, North" and "California, South" so I've made a modified list but I'm not to happy about that. And there are a number of states like this in your data base but not in the data in sheet 1.

Then are you going to do separte runs or do you plan to save old data and update with new?

At the moment the macro deletes all previous sheets except the first two. It then adds the name of the sales representative in column K, makes a unique list of all the names and sets up an autofilter for each person.

Result is copied to a sheet with sales representatives name. Have a look and see if this is something that you could use.

To test run macro "FindRep"

Alf

[kbon115]

Alf,

I really appreciate the help and the macro you've come up with.

Yeah the list of territories for each rep can be a little troublesome. Modifying it was the right thing to do. Other than New York, the split states (California, North+South) are owned by the same rep. The report that I run from SAP doesn't know to split the states based on where the city is. That's something I can alter myself though.

I plan on saving and emailing out the results once per week to the sales reps so they have a heads up on accounts with credits. So it'll be new data the next week.

From what I can tell so far, the macro does exactly what I need it to. Thank you so much. My workaround was creating a new column to the left of the data provided and using an if(D2="Alabama","Bob Smith",if(D2="Alaska",...... etc. for every single state and rep. Then I set it up into a table and auto filtered to organize it. From there I copied and pasted each section into a worksheet for each rep. Same end result, just more of a workaround.

Once again, thank you for helping me with this task, Alf. I will have to work on learning more about VBA and macros.

[Alf]

You are welcome. If there are any modifications or if you do have questions about the macro don't hesitate to ask.

If you are going to mail the results every week then perhaps one could have a third sheet as a template and just let the maco fill in the relevant data in order to get a more "professional" looking report?

Alf