Dodds Top 250 Forward - OVERALL (1 Viewer)

[george]

I see he has them posted by overall value.

I'd like to be able to sort this by position and value.

I know there is an excel command to ignore the letter and/or number.

Can someone tell me how to do this?

TIA

 

[Hedgehog]

I see he has them posted by overall value.I'd like to be able to sort this by position and value.I know there is an excel command to ignore the letter and/or number. Can someone tell me how to do this?TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

 

[cjack]

I see he has them posted by overall value.I'd like to be able to sort this by position and value.I know there is an excel command to ignore the letter and/or number. Can someone tell me how to do this?TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

He used to, but it doesn't look like he did that this time.You should be able to use the concatenate function which will allow you to ignore the characters after the two character position ID. Then you can sort by position. May have to make some adjustments for Kickers if that character ID is only one character.

 

[Short Corner]

I see he has them posted by overall value.I'd like to be able to sort this by position and value.I know there is an excel command to ignore the letter and/or number. Can someone tell me how to do this?TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

He used to, but it doesn't look like he did that this time.You should be able to use the concatenate function which will allow you to ignore the characters after the two character position ID. Then you can sort by position. May have to make some adjustments for Kickers if that character ID is only one character.

Sort the whole list by Column B. This will separate them by position. Then go and sort each position block by Column A.

 

[djjosee]

I see he has them posted by overall value.

I'd like to be able to sort this by position and value.

I know there is an excel command to ignore the letter and/or number.

Can someone tell me how to do this?

TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

He used to, but it doesn't look like he did that this time.You should be able to use the concatenate function which will allow you to ignore the characters after the two character position ID. Then you can sort by position. May have to make some adjustments for Kickers if that character ID is only one character.

You lost me here ...Yeah, they had the information by position last year.

 

[Kleck]

Oops, not working. If Def1 was Def01, ect., you could do a copy, paste special text, data sort.

In the MyFBG area, there is a custom top 200 for your league scoring if you have it set-up. There you can look at individual positions.

 

[cjack]

I see he has them posted by overall value.I'd like to be able to sort this by position and value.I know there is an excel command to ignore the letter and/or number. Can someone tell me how to do this?TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

He used to, but it doesn't look like he did that this time.You should be able to use the concatenate function which will allow you to ignore the characters after the two character position ID. Then you can sort by position. May have to make some adjustments for Kickers if that character ID is only one character.

Sort the whole list by Column B. This will separate them by position. Then go and sort each position block by Column A.

I think the problem is that the position ID has a number after it (ie., RB1, RB2, etc.), so you can't just sort by position. First, you have to remove the number after the two character position ID. So, the concatenate function in Excel lets you remove unwanted characters from a cell, or column. You'll have to look it up in Excel to get the formula right because I don't quite remember, but it will let you keep a certain amount of characters, in this case two, from a cell, and omit anything after those first two characters.Add a column in next to the position column. Use the Concatenate function from Excel to drop any characters after the two character position ID. Then, sort the new column by the position ranking.

 

[Random]

1 copy and paste into excel

2 sort by pos new

3 move pos new column to column k

4 move def section to column n

5 use text to columns (fixed width) to separate def and rank

6 repeat for the rest of the positions

7 move data back into list

8 sort by pos and rank

 

[cjack]

I see he has them posted by overall value.I'd like to be able to sort this by position and value.I know there is an excel command to ignore the letter and/or number. Can someone tell me how to do this?TIA

I thought they usually put it out in 2 tables - 1 overall and other by position

He used to, but it doesn't look like he did that this time.You should be able to use the concatenate function which will allow you to ignore the characters after the two character position ID. Then you can sort by position. May have to make some adjustments for Kickers if that character ID is only one character.

Sort the whole list by Column B. This will separate them by position. Then go and sort each position block by Column A.

I think the problem is that the position ID has a number after it (ie., RB1, RB2, etc.), so you can't just sort by position. First, you have to remove the number after the two character position ID. So, the concatenate function in Excel lets you remove unwanted characters from a cell, or column. You'll have to look it up in Excel to get the formula right because I don't quite remember, but it will let you keep a certain amount of characters, in this case two, from a cell, and omit anything after those first two characters.Add a column in next to the position column. Use the Concatenate function from Excel to drop any characters after the two character position ID. Then, sort the new column by the position ranking.

Okay, sorry guys, I was confusing the functions. Concatenate allows you to merge two cells. We want to eliminate text from a column of cells. It's the LEFT function which allows you to keep a certain number of characters from a cell, and omit the rest. So, you'll effectively remove the number at the two position code so RB1, RB2 will only read as RB. The formula is =LEFT(B1, 2) where B1 is the column with the new positional ranks, and 2 is the number of characters you want to keep. Then just copy that formula down. Then sort each position by trade value or column A to get the relative ranking by position.

 

[tomarken]

Import into excel. Should be six columns

in g2 insert "=left(b2)" without the quotes

copy this all the way down

sort by g ascending then by a ascending

 

[cjack]

Import into excel. Should be six columnsin g2 insert "=left(b2)" without the quotescopy this all the way downsort by g ascending then by a ascending

Doesn't he need to specify the number of characters he wants to keep, in this case 2?So, the formula would be =left(b2,2)?

 

[3C's]

There must be an easier way, but here is what I did. Using Openoffice so I think you replace the ; with ,

Copied and pasted the whole thing (including headers) into spreadsheet. Added 3 new columns next to (right of) column b (pos new). In column c I entered =MID(B2;1;2) to get the 2 letter position. In column d I entered =RIGHT(B2;LEN(B2)-2) to get the rank. In column e I entered =VALUE(D2) to get a numeric value (results in column d were text). Copy those 3 cells into the respective cells below them. Then I sorted by column c, then by column e, then by column I (trade value). Then removed the extra columns.

Since Def is 3 letters while the rest are 2, Def is messed up.

 

[3C's]

Or I suppose you could just get rid of the numeric value in rb/wr like suggested and sort by position and value.