Finding sheet duplication using excel VBA.

I need help in finding duplications within different tables within different sheets.

Sheet 1, shows an table (column A:F) as the following below:


1. US31397EWR25 FHR 3281 FT ABS CVR 10-16 11,220,000
2. US31397YTK72 FHR 3500 SE RMBS TALK 10-16 21,933,000
3. US31397PM493 FHR 3397 GS RMBS TALK 16-08 25,289,000
4. US31396VP850 FNR 2007-42 SD RMBS TALK 16-16 18,985,000
5. US31396YTV47 FNR 2008-20 SA RMBS TALK 13-16 13,359,000
6. US31395NN458 FNR 2006-58 SQ RMBS TALK 14-16 10,588,000
7. US31396PFR73 FNR 2006-124 SC RMBS TALK 15-00 21,509,000
8. US31396PVQ17 FNR 2007-23 SI RMBS TALK 16-00 20,115,000
9. US31396W3B04 FNR 2007-75 PI RMBS TALK 17-00 39,740,000
10. US31397WZL26 FHR 2009 GH CMBS TALK 15-45 13,675,000

Sheet 2, shows the same table (column A:F) as above. However due to some other calculation the table is formatted longer in length, such as seen below:

3. US31397EWR25 FHR 3281 CI RMBS TALK 15-16 11,220,000
4. US31397YTK72 FHR 3500 SE RMBS TALK 10-16 21,933,000
5. US31397PM493 FHR 3397 GS RMBS TALK 16-08 25,289,000
6. US31396VP850 FNR 2007-42 SD RMBS TALK 16-16 18,985,000
7. US31396YTV47 FNR 2008-20 SA RMBS TALK 13-16 13,359,000
8. US31395NN458 FNR 2006-58 SQ RMBS TALK 14-16 10,588,000
9. US31396PFR73 FNR 2006-124 SC RMBS TALK 15-00 21,509,000
10. US31396PVQ17 FNR 2007-23 SI RMBS TALK 16-00 20,115,000
11. US31396W3B04 FNR 2007-75 PI RMBS TALK 17-00 39,740,000
12. US31397WZL26 FHR 3485 SI RMBS TALK 15-00 13,675,000

I want to know, how can I show in sheet 2 table the duplication from sheet 1 table using the excel VBA.
The output is shown in sheet 2, through highlighting the same duplications as the table in sheet 1:

Sheet 2:
3. US31397EWR25 FHR 3281 CI RMBS TALK 15-16 11,220,000
4. US31397YTK72 FHR 3500 SE RMBS TALK 10-16 21,933,000
5. US31397PM493 FHR 3397 GS RMBS TALK 16-08 25,289,000
6. US31396VP850 FNR 2007-42 SD RMBS TALK 16-16 18,985,000
7. US31396YTV47 FNR 2008-20 SA RMBS TALK 13-16 13,359,000
8. US31395NN458 FNR 2006-58 SQ RMBS TALK 14-16 10,588,000
9. US31396PFR73 FNR 2006-124 SC RMBS TALK 15-00 21,509,000
10. US31396PVQ17 FNR 2007-23 SI RMBS TALK 16-00 20,115,000
11. US31396W3B04 FNR 2007-75 PI RMBS TALK 17-00 39,740,000
12. US31397WZL26 FHR 3485 SI RMBS TALK 15-00 13,675,000

Row 3 and 12 are different due to the difference between sheet 1 and sheet 2 tables.

Thank you for your time and help.

I need help in finding duplication within different worksheet tables.

I want to know, how can I show in sheet 2 table the duplication from sheet 1 table using the excel VBA.
The output is shown in sheet 2, through highlighting the same duplications as the table in sheet 1(please see attachment):

Thank you for your time and help.

Any help appreciated.




duplicate tables.xlsx

Dear arlu201

Ideally yes, all the columns need to be checked to be able to identify the duplication as i tired it with certain columns using if function formulas and it does not show the desired output.

Thank you for your time and response.

Kind regards
Misbah

Dear arlu201,

I tired using this If function formula:
=IF(AND($F2=Sheet1!$F2,$H2=Sheet1!$H2,$E2=Sheet1!$E2,$G2=Sheet1!$G2),Sheet1!I1,"").

But it does not show me the desired output in the sheet 2'a table.

I would really appreciate the help in finding the duplication.

Thank you for your time and help.

Kind regards
Misbah

is it possible for the duplication to take into also the black cells in column A or B. Each row in sheet 1 needs to be compared with sheet 2 table, to find accurate duplication, if possible.

is this plausible problem?

Thank you for your time and response.

I am very grateful to your help!

Kind regards
Miss

Yes.

It should be able to read the column's such as ISINs and name accurately compared to other sheet.

Thank you for your help and response.

Dear arlu1201

I found this VB function which allows to find duplicates within various sheet and display the data in sheet 1.

I am currently trying to manipulate this code below, so it compares all the table ranges on sheet 1 with sheet2 instead of the first rows.
I am getting compiling errors with the code below and would appreciate any help.

Please Login or Register  to view this content.

Thank you so much for your help and time.

Dear arlu1201,

Thank you so much for your help on this problem. I am very grateful to your help.

The code deletes last 10 rows of sheet 2 data, when its run, I am not sure why?

I have attached the file excel file with code in sheet 2.

Test duplication.xlsm

i have also attached the code below for further references.

Please Login or Register  to view this content.

If could please help me out with this small problem, i would be very grateful.

Thank you for your time and help.

Many thanks
Miss

Dear arlu1201,

Thank you for your response and help.

However, I copied the code into the workbook module and it still deletes the last 10 rows of sheet 2 data.

I still can not figure out why it does that.

Any assistant would be very helpful.

Thank you for your time and help.