Creating a list

R

Ripan

I have a list of 500 companies that have a an account manager assigne
to them.

I have a second sheet where Cell A1 will have the account manager name
Underneath, I want a list of all the companies that have that accoun
manager. I know the list the same length, but if I used an arra
formula, is there some way to have a list of all the companies wit
this account manager?

I basically am going to create some simple reports and rather tha
writing a macro, I want to use an array formula. If anyone knows how t
do this, I would appreciate some advice.

Thanks!

Ripa
 
D

Domenic

Assuming that your list of companies are in Column A, your list o
account managers in Column B, and that your data starts in Row 2...

C1 enter the manager of interest

D1 enter a 0

D2, copied down:

=IF((B2<>"")*(B2=$C$1),LOOKUP(9.99999999999999E+307,$D$1:D1)+1,"")

E1:

=LOOKUP(9.99999999999999E+307,D:D)

F2, copied down:

=IF(ROW()-ROW($F$2)+1<=$E$1,INDEX(A:A,MATCH(ROW()-ROW($F$2)+1,D:D,0)),"")

Hope this helps
 
Top