Duplicate rows in large data file

[COGICPENNY]

Hi,

I've attached a sample of the 4,000 rows of data that I need to analyze in a pivot table. Prior to doing that step, I need to "clean the worksheet" of duplicate data contained in rows. I first sorted the data by the Fiscal year 1 column (being that when they're duplicates the pricing is the same across Fiscal year 1-7)... unfortunately there wasn't an easier field to sort on like Item Number... So, what I would like to do is: Now that the data is sorted where if the rows are duplicated they appear under each other, I want to find the duplicate row(s) and delete them so that only ONE ROW will remain. To ensure there is no possibility of a row having the same dollar amount in the fiscal years (an oddity I'm sure), I'd like to be safe by "testing whether it's a duplicate" against the "Product EPM" (Column C) and "Product Family" (Column G) along with the revenue years (Columns H-M) -- these will NEVER change, but the other columns could which would make/break the duplicate test.

So in short what I'm saying is I need a formula that will test: Column C, Column G and Columns H-M against any other row -- if it's a match, it's a duplicate.

Thanks for the help.

[TMS]

In 2007, you can use Data | Remove Duplicates | select the columns to check.


Regards, TMS

[Leif Magnus]

Here is a macro also... Just open your VBA, insert module into that specific workbook and run it.

Please Login or Register  to view this content.

[FDibbins]

1 option would be to concatenate all those cells in each row, then based on thatdo your sort and deletion.

i have used this process in the past, and then used a formula to identify the duplicated.

so you would start off with (in col O)...=C2&G2&H2&I2&J2&K2&L2&M2 (if i understand you correctly, this will give you a totally unique "reference" for each row)

then in column P =IF(O2=O1,1,0)
both copied down
save the file (for safety), then value the formulas in colum P, sort on P, then delete all the rows with 1 in them
you can then sort on your foiscal year as before

[COGICPENNY]

I apologize for not responding... I thought no one ever replied to my thread because I failed to write "instant email notification"... will try both processes now... thanks, will keep you posted