Copying Cells on a diagonal?

[Rufus]

I need to copy cells on the diagonal for a large data set as follows

P1 P2 P3 P4 P5
Group 1 P1 5 4 5 4 3
Group 1 P2 5 4 5 4 3
Group 1 P3 5 4 5 4 3
Group1 P4 5 4 5 4 3
Group1 P5 5 4 5 4 3
Group 2 P1 5 4 5 4 3
Group 2 P2 5 4 5 4 3
Group 2 P3 5 4 5 4 3
Group 2 P4 5 4 5 4 3
Group 2 P5 5 4 5 4 3


I need P1's Self score, P2's Self Score, P3's self core etc for each
team lined up in a single column.

which for the above data would be

Self Rating
5
4
5
4
3
5
4
5
4
3

Is a formula like
=A2
=B3
=C4
=D5
=E6

the best way to do this and then cut and paste the formula for each
group?

Thanks,

Rufus

 

[Bernie Deitrick]

Fufus,

Certainly, making your group of five formulas and copying and pasting down to match your data set is
one way. That may become a problem when you change your data, so a flexible formula might be better
in the long run.

One way would be to use SUMIF, like so:

=SUMIF($B$1:$F$1,A2,B2:F2)

Where your identifiers are in column A and in row 1, and your scores are in columns B to F starting
in row 2. Enter that formula in G2, and copy down to match your data set.

HTH,
Bernie
MS Excel MVP

 

[Rufus]

Thanks for your reply Bernie.

I'm hoping to avoid the cut and paste option as I have large data sets
roughly 1000 people.

I don't need to sum anything but perhaps your formula doesn't add
anything up i will try it out.

I really just need the cells on the diagonal (self ratings) all merged
into one column.

Rufus

 

[Bernie Deitrick]

Rufus,

My formula only 'sums' one number, the number for which the header matches the row label.

If you don't want to be misled by the SUMIF, you could use either of these as well

=INDEX(B2:F2,1,MATCH(A2,$B$1:$F$1,FALSE))

=HLOOKUP(A2,$B$1:F2,ROW(A2),FALSE)

Just proving that Excel usually offers a number of ways to do the same thing....

HTH,
Bernie
MS Excel MVP