rows to columns

[jbelliot]

The attached mock Excel file shows what I currently have and what I need
Currently my scores show in one cell whereas I need them to spread across 3 columns in order to work out points for goals scored

[AliGW]

Are you still using Excel 2010?

[AliGW]

Maybe this:

=TRIM(LEFT(INDEX($A$3:$K$3,MATCH(A7,$A$2:$K$2,0)),2))

and this:

=TRIM(RIGHT(INDEX($A$3:$K$3,MATCH(A7,$A$2:$K$2,0)),2))

[jbelliot]

Many thanks for quick reply First of all I am using MS 365 Subscription and I will change my profile thanks for pointing out
Secondly where do I actually paste the formulaes ? In the cells I require ? or blank sheet?

[AliGW]

In B7:

=TRIM(LEFT(INDEX($A$3:$K$3,MATCH(A7,$A$2:$K$2,0)),2))

In D7:

=TRIM(RIGHT(INDEX($A$3:$K$3,MATCH(A7,$A$2:$K$2,0)),2))

Your profile is still showing Excel 2010.

[jbelliot]

Hi AliGW
That works perfectly thank you very much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[wk9128]

Cell B7 array formula , Drag down and accross

[jbelliot]

this worked ok apart from when I came to copy and paste into my spreadsheet the formulas would not work correctly I have attached basic copy of small part to show problem. Any ideas?

[wk9128]

Cell O4 ?

=IF(--K4="","",(IF($F4=Q4,2,)+(IF($B4=--I4,1,)+(IF($D4=--K4,1)+(IF($B4=--I4,AND($D4=--K4,1,AND($D4=--K4))))))))+IF(P4,1,0)

[jbelliot]

I think my formula OK its just that the pasted format is not being read correctly. I can get formula to work but I have to go in to cell I4 and and change 1 to number 1 same with K4. It seems as though when convert from rows to columns it has changed number format. Could this be right?

[davidjohn1645]

Convert a cell line to various segments or lines with Text to Columns and Paste Transpose capacities.
Convert a solitary line to various sections and lines with Transform Range.
Select the cell you need to change over, and click Data > Text to segments.

[jbelliot]

My formula to work out points is correct and i have tested it out time and again BUT only way I can get it work is by inputting scores manually. Whether I use TRIM or Clear the formula does not wotk after pasting. I must be doing something wrong but no idea what