How do I create a small array from a larger one?

[Luke]

I have a large spreadsheet (20,000+ rows) with company names (column A),
employee names (column B), and whether each employee is active ("1"=active,
"0"=inactive - column C). This spreadsheet is sorted by company name, then
by employee name.

On a separate sheet I have a drop-down where you select a company. That
selection populates a second drop-down with ALL the employees of that company
(getting the list from the first spreadsheet mentioned above). I am having a
difficult time coming up with an array formula that will allow me to populate
the second drop-down with only the ACTIVE employees of the selected client.
Can anyone help with this?

Thanks.

 

[Luke M]

An easy way to create this would be to create a PivotTable.
Selecting your raw data on Sheet1, go to Data, PivotTable. Click 'next'
(default choices), then make sure raw data is still selected and hit "finish".

You should now see a blank Pivot Table. Drag the Company name field into the
top "page field" area. Then, drag the Status/Active field also into the page
field area, just below Company name. Finally, drag employee name into the Row
field area.

You can now use the page field dropdowns to quickly select company/status,
and see all the employees.

Nice name, btw. ;-)

 

[Luke]

I appreciate the PivotTable suggestion. I may be able to make use of that in
a different spreadsheet. But in this case I really need to come up with a
formula (array or otherwise) that can generate a small array from a larger
one. Generating the large array isn't very difficult, it's getting rid of
all the data I don't need that's presenting the problem.

And the name is pretty cool. I really would have done a double-take if the
last initial had been the same, tool.

 

[Charles Williams]

Hi Luke,

If you email me off-list I have a function in Beta-test that sounds like it
does what you are looking for.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com