Index & Match

excel 2000

I am having trouble finding the proper function to arrive
at the following answer for this table:

employee store
john store1
dave store1
tom store1

bill store2
john store2
ken store2

dave & tom are at store1 all the time
bill & ken are at store2 all the time
john works both stores during the week

On my report, I want to show all the stores listed for
each employee

stores
employee column 1 | column 2
bill store2 |
ken store2 |
john store1 | store2
dave store1 |
tom store1 |

johns information has to appear in the same row. I've
totalled all his sales infomation together.

i've tried several functions, but can't get the right
combination.

do i use index & match, countif, choose?

any help???

thank you
greg

Greg

Data is in the range A1:B8 as described. The output is in
2 columns.

In cell C2 enter the formula
=COUNTIF($A$2:A2,A2)
and copy down.

In cell D1 enter the heading Employee and in the range D2 -
D6 enter the employee names.

In Cell E2 enter the formula
=INDEX($B$1:$B$8,SUMPRODUCT(($A$2:$A$8=D2)*($C$2:$ C$8=1)
*ROW($A$2:$A$8)))
and copy down to E6.

In Cell F2 enter the formula
=IF(SUMPRODUCT(($A$2:$A$8=D2)*($C$2:$C$8=2)*ROW($A $2:$A$8))
0,INDEX($B$1:$B$8,SUMPRODUCT(($A$2:$A$8=D2)*($C$ 2:$C$8=2)
*ROW($A$2:$A$8))),"")
and copy down.

Tony
-----Original Message-----
excel 2000

I am having trouble finding the proper function to arrive
at the following answer for this table:

employee store
john store1
dave store1
tom store1

bill store2
john store2
ken store2

dave & tom are at store1 all the time
bill & ken are at store2 all the time
john works both stores during the week

On my report, I want to show all the stores listed for
each employee

stores
employee column 1 | column 2
bill store2 |
ken store2 |
john store1 | store2
dave store1 |
tom store1 |

johns information has to appear in the same row. I've
totalled all his sales infomation together.

i've tried several functions, but can't get the right
combination.

do i use index & match, countif, choose?

any help???

thank you
greg
.

works great!

Thank You, Acw.

Greg

-----Original Message-----
Greg

Data is in the range A1:B8 as described. The output is
in
2 columns.

In cell C2 enter the formula
=COUNTIF($A$2:A2,A2)
and copy down.

In cell D1 enter the heading Employee and in the range
D2 -
D6 enter the employee names.

In Cell E2 enter the formula
=INDEX($B$1:$B$8,SUMPRODUCT(($A$2:$A$8=D2)*($C$2: $C$8=1)
*ROW($A$2:$A$8)))
and copy down to E6.

In Cell F2 enter the formula
=IF(SUMPRODUCT(($A$2:$A$8=D2)*($C$2:$C$8=2)*ROW
($A$2:$A$8))
0,INDEX($B$1:$B$8,SUMPRODUCT(($A$2:$A$8=D2)*
($C$2:$C$8=2)
*ROW($A$2:$A$8))),"")
and copy down.

Tony
-----Original Message-----
excel 2000

I am having trouble finding the proper function to
arrive
at the following answer for this table:

employee store
john store1
dave store1
tom store1

bill store2
john store2
ken store2

dave & tom are at store1 all the time
bill & ken are at store2 all the time
john works both stores during the week

On my report, I want to show all the stores listed for
each employee

stores
employee column 1 | column 2
bill store2 |
ken store2 |
john store1 | store2
dave store1 |
tom store1 |

johns information has to appear in the same row. I've
totalled all his sales infomation together.

i've tried several functions, but can't get the right
combination.

do i use index & match, countif, choose?

any help???

thank you
greg
.

.