transforming rank data

[David Schwartz]

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

 

[Pete_UK]

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)&":"&COLUMN(B2)),0)

This was then copied across to C11, 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

 

[David Schwartz]

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

 

[Pete_UK]

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

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

Pete