Excel tips or tricks for duplicate content madness?
-
Dearest SEO Friends,
I'm working on a site that has over 2,400 instances of duplicate content (yikes!).
I'm hoping somebody could offer some excel tips or tricks to managing my SEOMoz crawl diagnostics summary data file in a meaningful way, because right now this spreadsheet is not really helpful. Here's a hypothetical situation to describe why:
Say we had three columns of duplicate content. The data is displayed thusly:
|
Column A
|
Column B
|
Column C
URL A
|
URL B
|
URL C
|
In a perfect world, this is easy to understand. I want URL A to be the canonical. But unfortunately, the way my spreadsheet is populated, this ends up happening:
|
Column A
|
Column B
|
Column C
URL A
|
URL B
|
URL C
URL B
|
URL A
|
URL C
URL C
|
URL A
|
URL B
|
Essentially all of these URLs would end up being called a canonical, thus rendering the effect of the tag ineffective. On a site with small errors, this has never been a problem, because I can just spot check my steps. But the site I'm working on has thousands of instances, making it really hard to identify or even scale these patterns accurately.
This is particularly problematic as some of these URLs are identified as duplicates 50+ times! So my spreadsheet has well over 100K cells!!! Madness!!! Obviously, I can't go through manually. It would take me years to ensure the accuracy, and I'm assuming that's not really a scalable goal.
Here's what I would love, but I'm not getting my hopes up. Does anyone know of a formulaic way that Excel could identify row matches and think - "oh! these are all the same rows of data, just mismatched. I'll kill off duplicate rows, so only one truly unique row of data exists for this particular set" ? Or some other work around that could help me with my duplicate content madness?
Much appreciated, you Excel Gurus you!
-
Choose one of the URL's as the authoritive and remove the dupped content from the others.
-
FMLLC,
I use Excel 2010 so my approach would be as follows:
-
Make a backup copy of your file before you start.
-
You will need to sort each row by value, but Excel has a 3 sort level limit, so you will need to add a macro.
-
Assuming your data starts in A1 and has no header row, Put it in a general module, go back to excel, activate your sheet, then run the macro from Tools=>Macro=>Macros.
Sub SortEachRowHorizontal()
Dim rng As Range, rw As Range
Set rng = Range("A1").CurrentRegion
For Each rw In rng.Rows
rw.Sort Key1:=rw(1), _
order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight
Next
End Sub
- Then Highlight all your cells and then go to Data -> Remove Duplicates
The result should be all unique rows. I hope this helps.
-
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
Why am I getting all these duplicate pages?
This is going for basically all my pages, but my website has 3 'duplicates' as the rest just have 2 (no index) Why are these 3 variations counting as duplicate pages? http://www.homepage.com http://homepage.com http://www.hompage.com/index.php
Moz Pro | | W2GITeam0 -
Is The Number of Duplicate Pages reduced after adding canonical ref to the dupe versions ?
Hi Is the number of duplicate pages reported in a dupe page content error report reduced on subsequent crawls, if you have resolved the dupe content problem via adding the canonical tag to duplicate versions (referring the original page). Like it would if you were solving the problem via a 301 redirect (i think/presume) ? Cheers Dan
Moz Pro | | Dan-Lawrence0 -
Error in Moz duplicate content reports
Hi - I've run the Moz campaign on a client's site. Moz is saying that there are duplicate content errors, and when I look at the errors it is showing that they are all to do with the non-www URLs having being duplicated in the www form of the URLs. However this is not the case - all the non-www URLs are all 301 redirected to the www URLs. Is this an error in the Moz tool? Has anybody experienced something similar?
Moz Pro | | rorynatkiel0 -
Results still being seen for old deleted content
Hi, Our site has a blog. We had "Categories" and "Tags" in our blog. This caused SEO chaos with Duplicate content and 404 Errors. We removed all the Categories and Tags about a month ago. Why would these issues still be showing up on Moz as errors on our Crawl Diagnostics page? Do we need to do some thing else other than just remove them from our blog. Thanks
Moz Pro | | Studio330 -
Duplicate content
Hi Since adding blog to a site semoz is reporting increased duplicate content warning on seomoz crawl error tool such as: /blog/category/easter being a duplicate of blog/2013/03 Does this type of dupe content matter ? If so how do you stop this ? Also pages and pages of dupe content reported from internal/site search results, such as: /catalogsearch/result/index/?q=mens+fashion being a duplicate of /catalogsearch/result/?q=mens+fashion Does this matter need to be fixed or since internal site search not an issue and can just ignore, if it is an issue what do you need do to fix this type of dupe content ? Cheers Dan
Moz Pro | | Dan-Lawrence0 -
Duplicate Errors found in my search
I have run my 1st site check with SEOMOZ and have 4000+ errors. The "duplicate Page Content" culprit appears to be a extended url that keeps showing as duplicating. This is only a customer log-in and can be redirected back to the main cust log in page, but is there a short way of doing it (rather than 4000x 301's)? The format of the url is: http://www.????.com.au/default/customer/account/login/referer/aSR0cDovL3d3dy1234YWNiYW Thanks
Moz Pro | | Paul_MC0 -
Upper and lower case spelling = dupe content?
Hi All, I've looking at my Crawl Diagnostics Summary and working on getting my site errors down as low as possible. One thing I'm noticing is that in the "Other URLs" column I'm seeing a lot of 1s. When I click on the number, it is showing me the exact URL with an upper case category title. For example, it appears like it's telling me that these two URLs are considered duplicate content: http://mysite.com/Category http://mysite.com/category Is that right? Does google care about upper and lower case spelling?
Moz Pro | | shawn810 -
Should I worry about duplicate content errors caused by backslashes?
Frequently we get red-flagged for duplicate content in the MozPro Crawl Diagnostics for URLs with and without a backslash at the end. For example: www.example.com/ gets flagged as being a duplicate of www.example.com I assume that we could rel=canonical this, if needed, but our assumption has been that Google is clever enough to discount this as a genuine crawl error. Can anyone confirm or deny that? Thanks.
Moz Pro | | MackenzieFogelson0