Find and swap data

[yjyja]

Hi guys,

Supposed the original data is shown as below, with each name assigned with a score in each row.

A B C D E F G H

John 3 Mary 2 Paul 2 Peter 1 (In round #1, John got "3", Mary got "2" etc)

Mary 4 Peter 3 John 4 Paul 1 (In round #2, John got "4", Mary got "4" etc)


Is there any way I can rearrange the order of the data so that for each row, Peter and his score will come first, then Mary and her score, then Paul and his and then John's? (desired outcome as shown below)

A B C D E F G H

Peter 1 Mary 2 Paul 2 John 3 (correspond to round #1 above)

Peter 3 Mary 4 Paul 1 John 4 (correspond to round #2 above)

Many Thanks for your help! I really appreciate it!

[mikeTRON]

You would likely need to post an example with an excel workbook for me to assist. Can you create an example with sample data and a desired outcome?

[yjyja]

Hi ,

Thank you very much for your reply.

In the attached file, you will find the original data and the desire outcome.

I am trying to rearrange original data so that for each row, each name and the according score will be shown with the order "Peter and his score, Paul and his score, Mary and her score and then John and her score".

Thank you for your help in advance!!

[Special-K]

You'll proibably have to adjust this

In A14-A17 put the names Peter, Paul, Mary, John (the order in which you want them)

in B14
=INDEX($B2:$I2,1,MATCH(INDIRECT("A"&INT(COLUMN()/2)-1+14),$B2:$I2,0))
in C14
=INDEX($B2:$I2,1,MATCH(INDIRECT("A"&INT(COLUMN()/2)-1+14),$B2:$I2,0)+1)

Select B14:C14 and copy
Now paste across to D14:I14
Select B14:I14 and copy
Paste down as far as row 16