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
-
Unsolved Next JS and Missing content
Hello
Moz Pro | | 4thWhale
We recently migrated our page to next JS which is supposed to be great for SEO
On almost all our pages we are getting the same errors Missing Canonical Tag Missing Title Missing or Invalid H1 Missing Description We don't understand this because we have all of that content on every page. We believe that maybe NextJs is having a incompatibility with Moz. Has anyone had any experience with this?0 -
Moz isn't recognizing content on our website hosted on bubble.is
Hi all. We have a bubble.is site and it's difficult to get Moz to recognize the text on the site (even though Google seems to recognize it most of the time). The pages are recognized...just not the text. **In case you want to play around: ** https:getmarlow.com/about https://getmarlow.com/article/gather-data-on-where-your-time-and-energy-go-1579415891021x868278876529492000
Moz Pro | | Marlow20 -
Why is Moz Reporting as Duplicate Page Titles?
Our most recent MOZ crawl campaign is reporting 931 duplicate page title errors, most of which are "Product Review" pages like the following. Although there is only one review on this page, http://www.audiobooksonline.com/Cell_Stephen_King_unabridged_compact_discs.html, MOZ is reporting 15 duplicate page title, four of which I present below. http://www.audiobooksonline.com/reviews/review.php/full/0743554337/0/name/desc
Moz Pro | | lbohen
http://www.audiobooksonline.com/reviews/review.php/full/0743554337/0/rating/asc
http://www.audiobooksonline.com/reviews/review.php/full/0743554337/0/rating/desc
http://www.audiobooksonline.com/reviews/review.php/full/0743554337/0/state/asc Why is MOZ reporting these "pages" as duplicate page title errors? Are these errors hurting our SEO? How to fix?0 -
How to export keyword list to duplicate for another campaign in Moz?
I've create a couple campaigns (sub-domains) but I want them to largely have the same keywords as my other already create campaigns. How can I bring across these keywords? Export and then import them somehow?
Moz Pro | | BlueLinkERP6 -
Can't figure out why some of my pages are duplicate content
Within the crawl diagnostics area I'm getting duplicate page content issues on several pages. I don't know why, would anyone be able to tell me how these links are duplicate so I can fix them? http://www.sagenews.ca/Column.asp?id=3010 http://www.sagenews.ca/Column.asp?id=2808 http://www.sagenews.ca/Column.asp?id=2998 http://www.sagenews.ca/Column.asp?id=2837 http://www.sagenews.ca/Column.asp?id=2981
Moz Pro | | INMCA0 -
Duplicate Content and Titles in SEOMoz reports
I've had to rename some of the pages on my site and also move them to different locations. I placed a rel="canonical" on the old page pointing to the new one. The reports on my PRO Dashboard are telling me that I have Duplicate Content and Page Title errors. Do the SEOMoz automated reports take the rel="canonical" link into consideration or do I need to remove these pages and do a 301 redirect from the old to the new page?
Moz Pro | | TRICORSystems0 -
RSS feed showing up as duplicate content
Hi, I've just run an SEOMOZ Pro scan for the first time and it is picking up duplicate content errors from the RSS feed. For some reason it seems to be picking up two feeds, for example: http://blog.clove.co.uk/2009/05/13/htc-touch-diamond2-review/feed/ http://blog.clove.co.uk/2009/05/19/htc-touch-diamond2-review-2/feed/ Does anyone know why this is happening and how I can resolve this? Thanks
Moz Pro | | pugh0 -
Is there a Tool to compare Duplicate content for non web Live content?
Is there a tool that can give me % of duplicate content when comparing two pieces of content that are not Live on the web? Like copyscape but for content that may not be indexed by copyscape or not live on the web? Does Word or any other program allow you do do this?
Moz Pro | | bozzie3110