Count the number of duplicates in a sheet, report the duplicate cells to a new sheet.

hey everyone , I have been trying to write a macro which will check for the duplicate rows in a sheet for a given cell conditions and later copy the duplicates to a new sheet and report how many times that row/stirng is repeated.

I wrote a macro and it doesn't work saying "Run Time Error"- Application undefined or object defined error.

Attached is the excel file showing the index sheet (where the macro has to do the search of duplicates) and the sample sheet (shows the result on what the macro should report). I already tried to write a macro and it shows some errors. Please look at the excel file for better understanding.

For ex:
In the "index sheet"(in the excel file) i have the following columns:

the stack-up column is has 2T/3T, so the macro should loop through the row which has the cell value as 2T(skip the row if it is 3T) and read Material1 (mat1) , Material 2(mat2) under the respective columns in that row and loop through the remaining rows and check if there are any duplicates for the same material1, material 2 combination irrespective of thickness values & job nos. and report either it a unique/duplicate row(no.of duplicates) in a new sheet with that material combination.

JOB NO STACK-UP MAT 1 THICK 1 MATERIAL 2 THICKNESS 2 MATERIAL 3 THICKNESS 3
mm mm mm

C338-2600-6A 2T DP600 0.8 DP600 1 N/A N/A
C338-2600-7A 2T HSLA300 1.9 DP600 0.8 N/A N/A
C338-2600-8A 2T HSLA300 1.8 DP600 1.3 N/A N/A
C338-2600-8B 2T HSLA300 1.8 DP600 1.3 N/A N/A
C338-2600-10B 3T MILD STEEL 3 0.6 MILD STEEL 3 0.75 HSLA300 1.8
10007-3 3T DP600 2 DP600 2 DP600 2
10007-4-PA 2T HSLA300 2.5 HSLA300 2.9 N/A N/A
10007-4-PB 2T HSLA300 2.9 HSLA300 2.5 N/A N/A
10007-5-PA 3T LOW CARBON MILD STEEL 1.1 HSLA300 2.5 HSLA300 2.9
3732r2 2T HSLA550 1.3 USIBOR 1.4 N/A N/A
3732r4-PA 2T USIBOR 1.4 HSLA350 2 N/A N/A
3732r4-PB 2T HSLA350 2 USIBOR 1.4 N/A N/A


Below shows how the macro should report the result in a new sheet called material.

MATERIAL 1 MATERIAL 2 No.of Records
DP600 DP600 1
HSLA300 DP600 3
HSLA300 HSLA300 2
HSLA350 USIBOR 1
USIBOR HSLA350 1
HSLA550 USIBOR 1

Attached is the excel file showing the index sheet (where the macro has to do the search of duplicates) and the sample sheet (shows the result on what the macro should report). I already tried to write a macro and it shows some errors.

Thanks for your help in advance, either editing the macro i have written and a new code is fine.

Hey Martin,

The idea of concantenatning material 1& 2 is good, but i see that irrespective of the stack-up type you combined the materials. I want to count(take rows in consideration) the duplicates only if the cell value is 2T under the stack-up column else ignore that material combination. The number of rows keeps on growing (i.e i will have more material combinations) in the future and will this idea work in that case? Can i hide the Matcomb column and still will the pivot table idea work?

Thanks for your help in advance.

I dont see any macro for the pivot table generation in the excel file you made available here. It the code is available, it will be helpful for me in case i have a similar problem in future. The other issue is, for example, the mat combo of: USIBOR - HSLA350 = HSLA350 -USIBOR, in that case instead of having two seperate stack-up combinations and no.of records =1 for each combo, can we make them as one single mat combo but the no.of records =2?

Thanks for your help in advance