Large excel file I m trying to find duplicates and copy the rest of the cells across the duplicate to the duplicate it finds

Question

There are 65,000 pieces in my enormous excel spreadsheet; their part numbers are in column A. The part number is followed by numerous columns of crucial information (B is retail price, C is my cost, D is the weight, E description and so on until J) I recently received a list of 16,000 new parts that have been replaced older ones. The new list only includes the new part number in column A and the older one in column B.

The information from the columns next to that A cell should be copied into the columns next to this duplicate if B65001 matches anything in columns A1–A65000, but I'm not sure how to do it.

Answer 1

Copy the column from the other sheet into the one where you wish to search for duplicates. Write this straightforward formula, but divide the database into smaller sections to prevent system lag.

=IF(COUNTIF($A$2:$A$8, $A2)>1, "Duplicate", "")

Use this array formula to count Duplicates,

=ROWS($A$2:$A$8)-SUM(IF( COUNTIF($A$2:$A$8,$A$2:$A$8)=1,1,0))