Which functions will do this?
 

I'm looking for a formula (or combination of several) that will help me
transform columns of repeated data into a horizontal format.

Here's my sample data set:

Task Area Job Task 10 20 30
A 1 10 A X
A 2 20 B
A 1 30 C
A 4 40 D
A 3 50 E
I want to be able to match up the task and the job to solve for "X".
Essentially, I want to look for the specified task (in this case it's "A")
and find the job that's called out in the row above it's location (in this
case it's "10"). I've tried using INDEX & MATCH but haven't been able to
figure it out.

Which functions will do this?
 

I would write a macro that would realign your data

send me your sheet and I will add it for you



PointerMan wrote:
I'm looking for a formula (or combination of several) that will help me
transform columns of repeated data into a horizontal format.

Here's my sample data set:

Task Area Job Task 10 20 30
A 1 10 A X
A 2 20 B
A 1 30 C
A 4 40 D
A 3 50 E
I want to be able to match up the task and the job to solve for "X".
Essentially, I want to look for the specified task (in this case it's "A")
and find the job that's called out in the row above it's location (in this
case it's "10"). I've tried using INDEX & MATCH but haven't been able to
figure it out.

--
Anthony Prescott
http://www.apofficesolutions.co.uk

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

Which functions will do this?
 

Hi,

Try
=SUMPRODUCT(--($A$2:$A$16=$D2),--($C$2:$C$16=E$1),$B$2:$B$16)

This assumes your data are in columna A:C starting with titles on row 1. It
also assumes that the first task is in D2 and the first job in E1 in your
output grid.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"PointerMan" wrote:

I'm looking for a formula (or combination of several) that will help me
transform columns of repeated data into a horizontal format.

Here's my sample data set:

Task Area Job Task 10 20 30
A 1 10 A X
A 2 20 B
A 1 30 C
A 4 40 D
A 3 50 E
I want to be able to match up the task and the job to solve for "X".
Essentially, I want to look for the specified task (in this case it's "A")
and find the job that's called out in the row above it's location (in this
case it's "10"). I've tried using INDEX & MATCH but haven't been able to
figure it out.