Merging/Averaging a List of Rankings

Hey guys, glad I found this resource. I'm currently working on an assignment where I have 2 lists, each ranking websites based on traffic. The first list is very basic has two columns: ranking and domain name. The second is the same, but with slightly different rankings and a few domain names that are hidden. I'm trying to figure out a way to average the rankings so that it culminates into one coherent list. Any suggestions on how to approach this?

Can you upload an example sheet? And I don't understand the hidden domain names part... and why only a few are hidden.

- Moo

Sure, where should I upload it to?

Thanks the response, Moo. It's sort of what I'm referring to. I've attached a sample of what I mean.

example.xlsx

Essentially what I want is to combine columns A and D with E and F to create an averaged ranking. Additionally, the "Hidden profile" in F25 is an example of what I want to avoid, as there are many incidences in the full list of column F where this occurs and would mess up the combined rankings if I were to merely merge the two.

See my attached sheet.. let me know if it does what you want. I wish I knew VBA... would have been, I'm sure, much easier.

- Moo

Originally Posted by FDibbins

try this in G2, copied down...
=IFERROR((E2+INDEX($A$2:$D$30,MATCH(F2,$D$2:$D$30,0),1)/2),"")
then in H2, copied down...
=SMALL($G$2:$G$30,ROW(A1))

FDibbins, can you explain what this does? When I try it, I get a ton of half numbers. Sorry, excel-newb here.

Originally Posted by Moo the Dog

See my attached sheet.. let me know if it does what you want. I wish I knew VBA... would have been, I'm sure, much easier.

- Moo

Moo, thanks for the help. I can't open it on my Mac right now for some reason. I'll try it again later on my PC.

Moo, that was exactly what I needed. Your excel skill are very impressive? My only question is, how would I go about editing it to make it work for much longer lists? (couple thousand each)

Howard,

The formulas in C2 and G2 will currently work down to 1,000 rows, if you want to add more, just change the 1000 to at least as long as the number of total items from both lists.

Fill down the formulas in cells D2, E2 and F2 the same number of rows - you don't have to change anything in them.

Should be good to go. Just don't perform a sort on Sheet 1. I copied the data in columns F and G and used 'Paste Special: Values' onto the 'Average Rankings' sheet, THEN sorted by the ranking column.

- Moo

Originally Posted by Moo the Dog

Howard,

The formulas in C2 and G2 will currently work down to 1,000 rows, if you want to add more, just change the 1000 to at least as long as the number of total items from both lists.

Fill down the formulas in cells D2, E2 and F2 the same number of rows - you don't have to change anything in them.

Should be good to go. Just don't perform a sort on Sheet 1. I copied the data in columns F and G and used 'Paste Special: Values' onto the 'Average Rankings' sheet, THEN sorted by the ranking column.

- Moo

Thanks so much, Moo! Ultimately, it works very well. The problem now is that because of how long the actual list is, filling out columns D and E keeps freezing my Excel before I get to the end. Would it be useful to do it in chunks instead? Anyway to curtail this? I don't mind waiting, but I do mind if Excel keeps crashing

Howard,

I figured that might be a problem with the amount of data being manipulated when talking about thousands of rows... if that is the case, splitting the list isn't really an option for what you want to do.

It sounds like a macro is going to be the way to go - like I said earlier, it will probably be a lot easier in the long run. I will see what I can come up with.

- Moo

Originally Posted by Moo the Dog

Howard,

I figured that might be a problem with the amount of data being manipulated when talking about thousands of rows... if that is the case, splitting the list isn't really an option for what you want to do.

It sounds like a macro is going to be the way to go - like I said earlier, it will probably be a lot easier in the long run. I will see what I can come up with.

- Moo

Moo, do you mind walking me step by step through what you did? I'm trying to learn excel, and I'm not really sure what you did with each prospective column. Thanks so much for the help!

Howard,

Since I am just learning VBA, I was doing a lot of experimenting and learning from a friend (Thanks so much, Paul) on how to best go about a solution for you that is less intensive than the workbook I posted earlier, and what we came up with is in the attached file.

You'll notice Sheet1 is blank. No worries. The file contains a macro called CopyColumns. You can run it by pressing Alt + F8, click on the macro name, then choose Run.

When you do, a unique list of domains will appear, along with their average ranks - and the list will be sorted by rank.

PLEASE READ THROUGH THE CODE USED IN THE MACRO (BELOW) - I ADDED A LOT OF COMMENTING IN IT SO YOU CAN SEE HOW THINGS WORK.

Please Login or Register  to view this content.

To edit the code, press Alt + F8 again, and instead of clicking 'Run', you should click 'Edit'. Then you can edit it. It will be important if you change the names of the worksheets, trust me!

- Moo