I have been looking at this for other reason buy I am wondering how to match two columns and carry the data over even when the columns dont match.
http://www.seoptimise.com/blog/2012/08/investigating-panda-duplicate-content-issues.html
What this does not take into account is the possibility that some URLs may no longer be in one set of vice versa.
So how do I match up following columns
Landing1, clicks1, position1, Landing2, clicks2, position2
So that Landing 1 and landing 2 are the same, and therefore a clean comparison is made. There could be 60K of these urls.
Sounds like a pivot query?
Any ideas please.
I don't fully understand what you mean - but I do a lot of column matching with the VLookup command - might be what you need.
You could split up the columns into two sheets, sort and then rejoin. That should line up the data you need -- if the landing pages are one for one.
If not, you may consider converting it into csv/tsv and having perl match it up for you. Perl is really good at this sort of stuff.
I don't quite understand either - I had something the other day where I wanted to locate all instances in one column where the data also appeared anywhere in the second column - is that the 'landing 1 and landing 2 are the same' bit?
After a lot of faffing I used
=IF(COUNTIF(R:R,E:E)>0,"both columns","column e only")
clearly change columns and labels to your own needs.
You could then seperate all 'doesn't appear in both columns' data and sort the rest so they match up exactly?
I'm guessing vlookup might be what you're after. If not, INDEX MATCH
http://support.microsoft.com/kb/214142
Terrible job of explaining, better idea below
Landing1 clicks1 position1 Landing2 clicks2 position2
url.com/page1.htm 12 1 url.com/page1.htm 1 1
url.com/page2.htm 65 5 url.com/page2.htm 5 2
url.com/page3.htm 785 45 url.com/page22.htm 6 34
url.com/page4.htm 625 12 url.com/page8.htm 4 5
url.com/page5.htm 3245 12 url.com/page10.htm 74 67
url.com/page6.htm 2458 0.3 url.com/page6.htm 5 8
url.com/page7.htm 21 1 url.com/page8.htm 7 89
url.com/page8.htm 2348 25 url.com/page74.htm 5 4
url.com/page9.htm 214 2 url.com/page.htm 4 5
url.com/page10.htm 2145 1 url.com/page10.htm 78 7
As you can see the three columns on the left have different urls and data to the ones on the right, this may be due to some urls dropping etc. If they were the same I could order on the two columns , A and E, and the URLs and data would match.
Now this is not the case as they wont match up.
I dont know perl and i can imagine how it would be done in mysql, add a extra field which would be week1, week2 .
This seems like excel should do this in a flash.
I have fucked up some of the columns there, there is only ever 1 of each one.
you comparing 2 sets of data from different dates?
If so, I would start by separating your views.
1. Have 1 set of clean, consistent data.
2. Have a separate view for analysing the data
Data could be formatted
Date, Landing_URL, Clicks, Position
Then you could easily build a pivot table off the data set with
Landing_URL in the row labels
(Sum of) Clicks, (min of) Position in the Values
Date in the Column labels
I used to use excel all the time, but this baffles me.
The attached sheet will explain better. Columns Q and R I will do calculations in sort on them ultimately to get the biggest movers and shakers.
topfinds.co.uk/excel sort and match.xlsx
If I have understood you you right you want the two datasets to be in sync with each other?
If so this is a bit crude, but like others have said use the vlookup function for each column.
Create 3 extra colums, and use vlookup in the first one to grab the URL, then the second for the clicks and the 3rd for the position.
Once you have it all synced you can copy and paste just the values to get rid of any formulas and hey presto you have the data inline with each other.
I've attached a spreadsheet for ya :)
Thanks Chunford.
I had a bit of help and it seems a pivot table is the way:
http://www.youtube.com/watch?v=l0a0dCgFA5g