Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Integrating results from different pages? (No luck w/ INDEX & LOO
To put this in context, I'm working on my fantasy football spreadsheets. I
have various pages in a workbook that have similar data. So, for example, sheet one is from XYZ Fantasy Rankings and has the following data: [1] [QB] [Peyton Manning] [2] [QB] [Daunte Culpepper] [3] [QB] [Donovan McNabb] [4] [QB] [Trent Green] [5] [QB] [Kerry Collins] Sheet two contains the following: [1] [QB] [Peyton Manning] [2] [QB] [Donovan McNabb] [3] [QB] [Trent Green] [4] [QB] [Daunte Culpepper] [5] [QB] [Mark Bulger] (Sheet 2 is obviously fake!) The brackets mean the data are already set out in different cells, not that they appear in the spreadsheet. I would like to create a third sheet that parses the first two and gives me the following result: [QB] [Peyton Manning] [1] [1] [QB] [Daunte Culpepper] [2] [4] [QB] [Donovan McNabb] [3] [2] [QB] [Trent Green] [4] [3] [QB] [Kerry Collins] [5] [6] [QB] [Mark Bulger] [6] [5] How should I go about setting up this third sheet? And let's assume that I've used CONCATENATE and TRIM to get the names to all be in the same format on pages 2 and 3 (or, more likely, that I'll go in and fix them by hand so they are!) Also, sheets 1 and 2 might be of different lengths and there might be names that appear on one sheet but not the other. As a bonus question, for those names that don't appear on a list, I would like to force a value to return that is one greater than the highest number returned (so, for example, Bulger would be treated as if he had gotten a 6 ranking on sheet 1, even though he doesn't appear there). I've been stumbling through the INDEX and LOOKUP help files but just haven't had any luck. Thanks very much in advance |
#2
|
|||
|
|||
A little work...
First, I'd create a new worksheet (say sheet3) (to hold the combination of the original sheets). Copy all the names/positions from sheet1 to sheet3 (include one header row). copy all the names/positions from sheet2 to sheet3 (at the bottom of sheet1's data) Now you have all the names (some are duplicated, though) in sheet3 Select only column B (the column with Names) Apply data|filter|Advanced filter to get a unique list of names in column B (of sheet3). Debra Dalgleish has a nice instruction page at: http://www.contextures.com/xladvfilter01.html#FilterUR Now you copy those visible cells in columns A:B to columns C:D. (And delete column A:B--we're done with them! And select all the cells (ctrl-a, twice in xl2003) and autofit the row heights). Then you can use =vlookup() or =index(match()) to return the other info: You could look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html These formulas seemed to work ok: In sheet3, cell c2: =IF(ISERROR(MATCH(B2,Sheet1!C:C,0)),MAX(Sheet1!A:A )+1, INDEX(Sheet1!A:A,MATCH(B2,Sheet1!C:C,0))) and drag down. In sheet3, cell d2: =IF(ISERROR(MATCH(B2,Sheet2!C:C,0)),MAX(Sheet1!A:A )+1, INDEX(Sheet2!A:A,MATCH(B2,Sheet2!C:C,0))) and drag down. randomjohn wrote: To put this in context, I'm working on my fantasy football spreadsheets. I have various pages in a workbook that have similar data. So, for example, sheet one is from XYZ Fantasy Rankings and has the following data: [1] [QB] [Peyton Manning] [2] [QB] [Daunte Culpepper] [3] [QB] [Donovan McNabb] [4] [QB] [Trent Green] [5] [QB] [Kerry Collins] Sheet two contains the following: [1] [QB] [Peyton Manning] [2] [QB] [Donovan McNabb] [3] [QB] [Trent Green] [4] [QB] [Daunte Culpepper] [5] [QB] [Mark Bulger] (Sheet 2 is obviously fake!) The brackets mean the data are already set out in different cells, not that they appear in the spreadsheet. I would like to create a third sheet that parses the first two and gives me the following result: [QB] [Peyton Manning] [1] [1] [QB] [Daunte Culpepper] [2] [4] [QB] [Donovan McNabb] [3] [2] [QB] [Trent Green] [4] [3] [QB] [Kerry Collins] [5] [6] [QB] [Mark Bulger] [6] [5] How should I go about setting up this third sheet? And let's assume that I've used CONCATENATE and TRIM to get the names to all be in the same format on pages 2 and 3 (or, more likely, that I'll go in and fix them by hand so they are!) Also, sheets 1 and 2 might be of different lengths and there might be names that appear on one sheet but not the other. As a bonus question, for those names that don't appear on a list, I would like to force a value to return that is one greater than the highest number returned (so, for example, Bulger would be treated as if he had gotten a 6 ranking on sheet 1, even though he doesn't appear there). I've been stumbling through the INDEX and LOOKUP help files but just haven't had any luck. Thanks very much in advance -- Dave Peterson |
#3
|
|||
|
|||
Thanks for the help. I actually found an even simpler solution on her site
(more like an example of INDEX and MATCH that I could understand...) and came up with this formula: =INDEX(QB1!A:A,MATCH(A2,QB1!B:B,0)) So page 1 of my sheet is a list of every QB in the NFL, then page QB1 is one site's ranking of those QBs (rank in column A, name in column B, same throughout all my sheets). This works great, I copy that equation all the way down and I pull in the ranking for every player that has a ranking. I haven't figured out my second problem, which is how to force a ranking for players who don't appear in the list, but that's minor and I'll figure something out I'm sure. Here's my new problem: Most of my source come in the format "Rank. Firstname Lastname" I've figured converted those to columns, so I get the data as above. The problem is that some of the sites come in the format "Rank. Lastname, Firstname" So I text - columned those into three separate columns (let's call them D, E and F). Then I moved the Rank column (column E) over to column A. Then in B2, I put the formula =F2&" "&E2 So when you look at B1 and B2, it LOOKS exactly like "Rank. Firstname Lastname" but when I go to compare it, it doesn't show up as a match in the formula above. There are no leading or trailing spaces that I haven't accounted for. Is there a way for me to have text in the format "Firstname Lastname" match a cell that is a formula that gives the same result? "Dave Peterson" wrote: A little work... First, I'd create a new worksheet (say sheet3) (to hold the combination of the original sheets). Copy all the names/positions from sheet1 to sheet3 (include one header row). copy all the names/positions from sheet2 to sheet3 (at the bottom of sheet1's data) Now you have all the names (some are duplicated, though) in sheet3 Select only column B (the column with Names) Apply data|filter|Advanced filter to get a unique list of names in column B (of sheet3). Debra Dalgleish has a nice instruction page at: http://www.contextures.com/xladvfilter01.html#FilterUR Now you copy those visible cells in columns A:B to columns C:D. (And delete column A:B--we're done with them! And select all the cells (ctrl-a, twice in xl2003) and autofit the row heights). Then you can use =vlookup() or =index(match()) to return the other info: You could look at Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html and http://www.contextures.com/xlFunctions03.html These formulas seemed to work ok: In sheet3, cell c2: =IF(ISERROR(MATCH(B2,Sheet1!C:C,0)),MAX(Sheet1!A:A )+1, INDEX(Sheet1!A:A,MATCH(B2,Sheet1!C:C,0))) and drag down. In sheet3, cell d2: =IF(ISERROR(MATCH(B2,Sheet2!C:C,0)),MAX(Sheet1!A:A )+1, INDEX(Sheet2!A:A,MATCH(B2,Sheet2!C:C,0))) and drag down. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating results in formulas | Excel Discussion (Misc queries) | |||
Numbering pages | Excel Worksheet Functions | |||
numbering all pages in worksheet | Excel Discussion (Misc queries) | |||
How do I remove pages in Excel? | Excel Discussion (Misc queries) | |||
How do I delete pages? | Excel Discussion (Misc queries) |