Re: Integrating results from different pages? (No luck w/ INDEX &

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
>
.

Relevant Pages

  • Re: Rank with condition
    ... If I could send you my pared-down worksheet which contains the actual formulas and actual list, you could better understand why it ... my rank list also includes percentages which need to be excluded from consideration ... directly under the integer cells and therefore have a blank cell between each ranking formula. ...
    (microsoft.public.excel.misc)
  • Re: merge formulas?
    ... The formula references a range of cells, and it is supposed to rank ... The RANKfunction will duplicate rank ... give a serial ranking of values, ... me the same values of the whole formula in a single cell: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Ranking score
    ... > In particular they are asked to rank those obecject that they know. ... ranking the comparisons, suggested by Ray, could be ... Then do the same analysis using lines with 4 ratings, ... The cell would ...
    (sci.stat.math)
  • RE: How EXACTLY does Indexing Service determine rank
    ... the point that they are adjacent becoming a phrase and raising the rank ... The ranking mechanism is weighted so that the more highly inflected ... the linguistic engine and ranking algorithm ... Indexing Service is based on ranking formulas that are used everywhere from ...
    (microsoft.public.inetserver.indexserver)
  • Re: Rank with condition
    ... I can see that Biff sent you a subsequent ... to reflect the cell immediately above the formula, ... Unfortunately, my rank list also ... In the actual worksheet, I cannot exclude any of my integers ...
    (microsoft.public.excel.misc)