Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
I am creating a spreadsheet for a fantasy sport that will allow me to look at
the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
You could try a lookup, type the following in cell C1 on spreadsheet1
=LOOKUP(Spreadsheet1!B1,Spreadsheet2!B:B,Spreadshe et2!C:C) "Riptide" wrote: I am creating a spreadsheet for a fantasy sport that will allow me to look at the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
Since LOOKUP() requires the ADPs to be in a sorted list (sorted by name in
this case), VLOOKUP() is the better approach: =VLOOKUP(B1,Spreadsheet2!B2:C100,2,0) "Stephen C" wrote: You could try a lookup, type the following in cell C1 on spreadsheet1 =LOOKUP(Spreadsheet1!B1,Spreadsheet2!B:B,Spreadshe et2!C:C) "Riptide" wrote: I am creating a spreadsheet for a fantasy sport that will allow me to look at the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
Well I tried both options and all I get is #N/A. Perhaps the problem is that
when I say spreadsheet I actually referring to the sheets along the bottom. Sorry for this because I am sure it changes the formula. this is what I tried, modified in order to match the cells in my file: =VLOOKUP(A2,ADP!A2:B216,2,0) Column 'A' contains the players name both in the 'combine' sheet and the 'ADP' sheet, coulmn 'B' in the 'ADP' sheet lists the ADP which needs to be copied into column 'F' of the 'combine' sheet. Again sorry for the confusion, I wish there was a way to post it as it would certainly help out more. "Duke Carey" wrote: Since LOOKUP() requires the ADPs to be in a sorted list (sorted by name in this case), VLOOKUP() is the better approach: =VLOOKUP(B1,Spreadsheet2!B2:C100,2,0) "Stephen C" wrote: You could try a lookup, type the following in cell C1 on spreadsheet1 =LOOKUP(Spreadsheet1!B1,Spreadsheet2!B:B,Spreadshe et2!C:C) "Riptide" wrote: I am creating a spreadsheet for a fantasy sport that will allow me to look at the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
The spreadsheet/tabs concern is not the problem.
I suspect you do not have identical names in each list. Ignore the quotes below - they are intended to clearly indicate the comparison strings "Manning, Peyton" will NOT MATCH "Peyton Manning" will NOT MATCH "P. Manning" Likewise, a cell that contains "Peyton Manning " will NOT MATCH "Peyton Manning" So, once you have the names sync'ed up, the formula will work. "Riptide" wrote: Well I tried both options and all I get is #N/A. Perhaps the problem is that when I say spreadsheet I actually referring to the sheets along the bottom. Sorry for this because I am sure it changes the formula. this is what I tried, modified in order to match the cells in my file: =VLOOKUP(A2,ADP!A2:B216,2,0) Column 'A' contains the players name both in the 'combine' sheet and the 'ADP' sheet, coulmn 'B' in the 'ADP' sheet lists the ADP which needs to be copied into column 'F' of the 'combine' sheet. Again sorry for the confusion, I wish there was a way to post it as it would certainly help out more. "Duke Carey" wrote: Since LOOKUP() requires the ADPs to be in a sorted list (sorted by name in this case), VLOOKUP() is the better approach: =VLOOKUP(B1,Spreadsheet2!B2:C100,2,0) "Stephen C" wrote: You could try a lookup, type the following in cell C1 on spreadsheet1 =LOOKUP(Spreadsheet1!B1,Spreadsheet2!B:B,Spreadshe et2!C:C) "Riptide" wrote: I am creating a spreadsheet for a fantasy sport that will allow me to look at the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying cells with similar text
Found the problem, thanks for the help.
"Duke Carey" wrote: The spreadsheet/tabs concern is not the problem. I suspect you do not have identical names in each list. Ignore the quotes below - they are intended to clearly indicate the comparison strings "Manning, Peyton" will NOT MATCH "Peyton Manning" will NOT MATCH "P. Manning" Likewise, a cell that contains "Peyton Manning " will NOT MATCH "Peyton Manning" So, once you have the names sync'ed up, the formula will work. "Riptide" wrote: Well I tried both options and all I get is #N/A. Perhaps the problem is that when I say spreadsheet I actually referring to the sheets along the bottom. Sorry for this because I am sure it changes the formula. this is what I tried, modified in order to match the cells in my file: =VLOOKUP(A2,ADP!A2:B216,2,0) Column 'A' contains the players name both in the 'combine' sheet and the 'ADP' sheet, coulmn 'B' in the 'ADP' sheet lists the ADP which needs to be copied into column 'F' of the 'combine' sheet. Again sorry for the confusion, I wish there was a way to post it as it would certainly help out more. "Duke Carey" wrote: Since LOOKUP() requires the ADPs to be in a sorted list (sorted by name in this case), VLOOKUP() is the better approach: =VLOOKUP(B1,Spreadsheet2!B2:C100,2,0) "Stephen C" wrote: You could try a lookup, type the following in cell C1 on spreadsheet1 =LOOKUP(Spreadsheet1!B1,Spreadsheet2!B:B,Spreadshe et2!C:C) "Riptide" wrote: I am creating a spreadsheet for a fantasy sport that will allow me to look at the order that I think players are ranked compared to their average draft position. However, I have one spreadsheet that has all my player rankings and another that lists their average draft position(ADP). Since the order of players differs from their ADP it will take forever to insert each ADP for each player manually. Is their a formula I can use? Here is an example: Spreadsheet 1 Spreadsheet 2 (Ranking of players) (Average Draft Position) Name (B) ADP (C) Name (B) ADP (C) Player 1 ? Player 8 1.01 Player 2 ? Player 3 1.02 Player 3 ? Player 15 1.03 Player 4 ? Player 1 1.04 Player 5 ? Player 2 1.05 Is there a formula that I can type in SPreadsheet 1, Cell C1 that will Look in Spreadsheet 2, Column 'B' for a name that matches the name in Cell B1 in Spreadsheet 1? Then copy the information from Cell C1 in Spreadsheet 2 into Cell C1 in Spreadsheet 1. Sorry for any confusion, but I would greatly appreciate any help. FYI - Spreadsheet 1 is my master sheet and I wasn't able to find a site that provided both statistical performance and ADP on the same chart. Otherwise I would have just used the sort command. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CAN I CLUB SIMILAR EXCEL FILES WITHOUT COPYING AND PASTING | Excel Discussion (Misc queries) | |||
copying text between cells | Excel Discussion (Misc queries) | |||
Identifying cells with similar text | Excel Discussion (Misc queries) | |||
Copying cells with similar column values | New Users to Excel | |||
Counting similar text | Excel Worksheet Functions |