Highlight Duplicate Rows and Export to New Worksheet

[alanaduffy]

Hi all,

I am working with a large set of data (5000 rows and columns A:AP).

I am looking to find rows that are duplicate in their entirety, and then export them to a new sheet. So far I have used a formula to convert each row into a single string (=concat(A5:AP5), and from there I use conditional formatting to highlight duplicate values in the concatenated column, which shows me the rows that are duplicated. However, this only highlights the duplicated cells that contain the concat formula, and I need the highlight to extend across the entire row. In addition, I then need to export all duplicated rows to a new Excel sheet.

The ultimate goal is to be able to paste a new set of data into a sheet already containing data, then find and export rows that are duplicate between the new data set and what was already in the sheet. Does anyone know how to do this?

 

[Joe4]

Welcome to the Board!

However, this only highlights the duplicated cells that contain the concat formula, and I need the highlight to extend across the entire row.

What column are you putting the CONCAT formula in?
What Conditional Formatting formula are you using to look for duplicates?

In addition, I then need to export all duplicated rows to a new Excel sheet

So, let's say a particular row is duplicated 3 times (so there are 4 copies of it).
Do you want to move all 4 rows, or keep one and move 3?