Rearranging the data set based on multiple rules.

Hello everyone!

I'm having an issue reformatting a large data set that completely boggled my mind to the point at which I have no idea how to even start figuring it out...

Long story short, the data set comes from a graduate research of mine. Here the participants are able to choose some symbols and later rate them in terms of their payoff-predictive power. So, columns D to G are the frequencies of their choices, whereas columns S and T hold their ratings.

What I'm interested in is the frequency-rating slopes, which would either confirm or reject my hypothesis. In order to do create these slopes I need to organise the frequencies in a descending fashion (already done - columns I to L) and place the corresponding ratings into columns M to P.

The problem is to actually automate it (I've done it manually before, but the human error is getting out of hand).

So the algorithm should look something like this: Look up the number in the column I -> Find this number in the corresponding row in columns D to G -> Note the symbol (name of these column) -> Lookup a value for this participant and symbol in the columns S and T -> Place this value in column M. The same to be done for columns N, O and P.

The formula is ought to look like a puzzle, but I would appreciate if someone with more experience than I had a look at it.

The original excel file is attached.

Best regards and thanks for your help!

Originally Posted by martindwilson

in m2 you have 71 if this relates to star 28 ie value in chosen 1 I2 is 28 which corresponds with d2 why is the result not
28 star 64
from t112

Thank you for your response! I apologise if I didn't make myself clear in the original post! Column R stands for subjects (the same as the column A). So, what I'm interested in is what is the corresponding rating (reported1) for the most chosen symbol (chosen1). Here the 28 in M2 stands for star in D2-G2, so we look up the value for participant 1, star, which turns out to be 71.

Also I have noticed that sometimes the order is impossible to work out. E.g. In cases where either reported or chosen frequencies have multiple occurrences of one value. I was able to solve this problem by highlighting the part in question when to be able to make an adjustment if necessary. I'm not sure though if it is possible to do the same in an automated procedure.

Originally Posted by martindwilson

do you mean something like this

That is exactly what I needed! Moreover, I can use this for the data set with adjusted trial block divisions (here I used four trial blocks - column B). Thank you good sir!

I now wonder if my highlighting procedure is impossible to implement over questionable places in the data frame. E.g. M6:N6