Converting text from rows to columns

[Phyllis B.]

Hi,

Can anyone tell me the best way to accomplish this.

I have two worksheets the first on contains these columns and data

A B C D
1 EE# Qtr Ded Code Amount
2 1010 1 FIT 39.95
3 1010 1 SIT 4.65
4 1010 2 FIT 39.95
5 1010 2 SIT 4.65
6 1012 1 SDI 10.15
7 1012 2 SDI 10.15

My second worksheet looks like this

EE# Qtr FIT SIT SDI
1010 1
1010 2
1012 1
1012 2

I want to take the amounts from worksheet 1 column D and input them into the
appropriate column on worksheet 2 based on the EE#, Qtr and Ded Code. I
tried index match, but it kept returning either "False" or "#Value!". Please
help.

Thanks
phyllis b.

 

[Biff]

Hi!

Try this:

Enter this formula in Sheet2 C2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=$B2),--(Sheet1!$C$2:$C$7=C$1),Sheet1!$D$2:$D$7)

Copy across to E2 then down.

Biff

 

[Phyllis B.]

thank Biff, it worked like a charm. You're the best.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

thank Biff, it worked like a charm. You're the best.