up vote 0 down vote favorite
share [g+] share [fb]

I have an Excel spreadsheet with columns A and B, each containing book titles, ordered by ranking (top to bottom), from two different sources.

I would like to know which items both sources consider to be the highest ranked and I would like to have these entries in column C. Again, the books are already order by ranking so the only information available in each column is book titles.

link|improve this question
I don't think I understand. It sounds like you would have a column of book titles. Right beside it you have another column. Then this bit "I would like to know which items both sources consider to be the highest ranked". Wouldn't there just be one highest ranked item per source? So aren't you just saying "If A1 = B1 then put it into C1"? – EBGreen Sep 6 '11 at 18:11
You want to know where there is some agreement about the highest ranked books (e.g., LRRH, CC, and RiA, but not TGB)? – A Lion Sep 6 '11 at 19:29
I'm also unclear. If the two columns were numbered (1 at the top descending), are you looking for the book with the lowest sum? – StevenV Sep 6 '11 at 19:43
You need to define what "both sources consider to be the highest ranked" means. Then it will be a simple problem. – Lance Roberts Sep 7 '11 at 15:44
feedback

2 Answers

up vote 1 down vote accepted

You could assign a score to each book based on its ranking, combine the scores, and sort by composite score.

For example, if List 1 is:

  1. Book Alpha
  2. Book Beta
  3. Book Delta
  4. Book Epsilon

And List 2 is:

  1. Book Delta
  2. Book Alpha
  3. Book Epsilon
  4. Book Beta

Book Alpha was ranked #1 from Source 1 and #2 from Source 2. Its composite score would be (1+2)=3. Then sort by composite score; lower is better.

  1. Book Alpha 3
  2. Book Delta 4
  3. Book Beta 6
  4. Book Epsilon 7

This would be a rough amalgamation of the data, at best. But I hope that helps.

link|improve this answer
Yes, but is there a way to do this in Excel? As in your example, the each list is already ordered by a score: 1, 2, 3, 4. – opBoy2026 Sep 8 '11 at 8:04
Just make a column next to the names with the rankings (1,2,3,4, etc.)... If you start your column with the first 2 entries, then select them, then click and drag the lower-right hand corner down, Excel will fill in the rest automatically. – Chris Sep 8 '11 at 14:41
Let's say Column A is the first list of books, Column B is that list's ranking, Column C is the 2nd list of books, and Column D is the 2nd list's ranking. You can then sort Columns A and B by column A (alpha ascending), then sort Column C and D by column C (alpha ascending). Assuming the same books are in each list, the Composite Score (column E)=B+D. You can set Cell E1 to "=B1+D1" then fill down as before. – Chris Sep 8 '11 at 14:48
feedback
up vote 0 down vote

Then it's impossible... you would need to know how the lists are comprised (ordered), and if they are comprised using the same measurements (ex. total sales, total amount grossed....) then they can be, but because you say you don't have that data, then there is nothing you can do...

Think of it this way... How does Excel know when to put one book ontop of another? Only once you can tell Excel that can you expect excel to rak the way you want it to.

link|improve this answer
Come on, it's not impossible, he's just not defining what he wants. You could probably do it by assigning an integer value to each item in the list, then multiplying the numbers of identical items, and taking the lowest score. But who knows if that is what he wants. – Lance Roberts Sep 7 '11 at 17:18
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.