I have an interesting problem, I think. I've got ranking data in the
form of:

1 2 3 4 5
a c d b e
c a d b e
e a c d b

where a, b, c, d, and e are the various choices being ranked (3
responses in this sample). How can I programmatically create a matrix
as follows so that it scales easily to any number of responses?

a 1 2 2
b 4 4 5
c 2 1 3
d 3 3 4
e 5 5 1

TIA,
David

I put your first table in cells A1:E4 of Sheet1, and then in Sheet2 I
put a, b, c, d, e in A1:A5. I then put this formula in B1 of Sheet2:

=MATCH($A1,INDIRECT("Sheet1!"&COLUMN(B2)&":"&COLUM N(B2)),0)

This was then copied across to C1:D1, and then those 3 formulae were
copied down to row 5, giving your second table.

The formula might need some adjustment if you don't use the same
cells, but it is scalable to more responses - just copy further across
(although you will get #N/A for any blank entries in Sheet1).

Hope this helps.

Pete

On Jan 31, 12:06*am, David Schwartz wrote:
I have an interesting problem, I think. I've got ranking data in the
form of:

1 2 3 4 5
a c d b e
c a d b e
e a c d b

where a, b, c, d, and e are the various choices being ranked (3
responses in this sample). How can I programmatically create a matrix
as follows so that it scales easily to any number of responses?

a 1 2 2
b 4 4 5
c 2 1 3
d 3 3 4
e 5 5 1

TIA,
David

very cool. thanks. I wonder whether there might not be another
approach that didn't rely on a particular fixed position of the data?

You're welcome, David - thanks for feeding back.

Someone else might be able to suggest an alternative ...

Pete

On Jan 31, 6:00*pm, David Schwartz wrote:
very cool. thanks. I wonder whether there might not be another
approach that didn't rely on a particular fixed position of the data?