Excel tips or tricks for duplicate content madness?

[FMLLC]

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!

[Chenzo]

Choose one of the URL's as the authoritive and remove the dupped content from the others.

[rmontanez]

FMLLC,

I use Excel 2010 so my approach would be as follows:

1. Make a backup copy of your file before you start.

2. 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.

3. 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

4. Then Highlight all your cells and then go to Data -> Remove Duplicates

The result should be all unique rows. I hope this helps.