Array by State

[Figboot]

Please see attachment...

I have a list of employees and the states they are assigned...the way the assigned states are listed is just like the example...one state per row (it will go down for all 50 states and contain additional employees, I just truncated the example here for simplicity)

Then I have a list of the states and the customers that have locations in those states. I need to show a list of all customers assigned to employee's states, and the number of locations in just those states. Any suggestions?

I appreciate the assistance...

[MarvinP]

Hi Figboot,

If you arrange your data a little differently, you can do a very easy and quick pivot table. See the attached.

[Figboot]

Thanks. I know pivot tables are made for this kind of thing, but I was looking to be able to display an employee followed by only the customers they cover as opposed to a table of all customers. Thought a type of array might do the trick. Looks like I might have to revert back to a pivot.

[Figboot]

...the more I look at this, I'm not sure a pivot will work. Mainly because each state has more than one customer, so in your example .xlsx in A21:D31, how would I display that AK has both Macy and Costco?

[MarvinP]

Hi,
See the attached with a second pivot.

[NBVC]

With a helper section to find matches... final output is as you desired..... see attached... note: There are some array formulas that need to be confirmed with CTRL+SHIFT+ENTER not just ENTER... those are the formulas with { } brackets around them.

[Figboot]

NBVC...I appreciate the help. Did you have the correct file attached? I couldn't find any arrays on yours.

[NBVC]

Sorry... I replaced the attachment above.... check again please.

[Figboot]

Perfect...exactly what I was looking for. Thanks for the assistance!