Thread: Need to find if duplicate values exist in a Master sheet, compare in New generated sh

Hi All,

I hope I'm posting in the right section. I have a problem that I think is quite difficult, but maybe not. I was going to attach the spreadsheet I was working with.

Need to find if duplicate values exist in a Master sheet, compare in New generated sheet and then display the newly added datas in output sheet.

What I want to do, is search for duplicates by comparing master and new sheet by matching the columns datas. Once that is complete, it needs to delete the row of duplicate entries and update the new entries in output sheet.

I have attached the example file for the reference. Which the output should look like.

Hello,

use a helper column in sheet New, starting in H2

=IF(ISNUMBER(MATCH(A2&B2&C2&D2&E2&F2,INDEX(Master!$A$1:$A$100&Master!$B$1:$B$100&Master!$C$1:$C$100& Master!$D$1:$D$100&Master!$E$1:$E$100&Master!$F$1:$F$100,0),0)),ROW(),"")

On the Output sheet, use in A2

=IFERROR(INDEX(New!A$1:A$100,LARGE(New!$H$2:$H$100,ROW(A1))),"")

copy across to column G and then copy down.

Adjust ranges to suit.

And for your next question, please don't start a poll.

cheers,

Hi Thank you,

It would be nice if you provide the macro for this output file. Since I have large number of data's to be input and everytime i have to manually compare the master sheet and new sheet which is taking long time.
Also the output should be result as I mentioned in the attached example. Hence all the columns should be matched with the master sheet and new sheet and the new entries alone should be displayed in the out put sheet. I will be really happy if you provide the solution for this.

I used formulas to achieve what you describe, not VBA. You don't need to compare anything manually. The formulas will do that and will create the result in the Output sheet.
Have you tried it? Do you get errors? If so, which?

Hi,

I have tried as you said above but I am getting error message as "#NAME? in the A2 column in the output sheet.

For better understanding I have attached the example 2 spreadsheet which I followed from your instructions. And if you found that this is incorrect.

correct the mistake and attach with the correct formula. It will helps me a lot.

Also I should use this for nearly 2000 cells.

Teylyn's formulas work perfectly. The only problem was that you copied column H too far down.

Cheers,

Post the file that shows the error message.

Your profile states that you are using Excel 2007.

Iferror() is not available before 2007. So use 2007 with the file and it will be fine.

Or change your profile to reflect your real Office version.

We ask that for a reason.

Replace the formula on the Output sheet. Start in A2 with

=IF(ISERROR(INDEX(New!A$1:A$100,LARGE(New!$H$2:$H$100,ROW(A1)))),"",INDEX(New!A$1:A$100,LARGE(New!$H $2:$H$100,ROW(A1))))

copy across and down.

Insert a new column H, so the helper column with the numbers is pushed to the right.

Into the new cell H2 enter

=IF(ISNUMBER(MATCH(A4&B4&C4&D4&E4&F4,INDEX(Master!$A$1:$A$100&Master!$B$1:$B$100&Master!$C$1:$C$100& Master!$D$1:$D$100&Master!$E$1:$E$100&Master!$F$1:$F$100,0),0)),INDEX(Master!$H$1:$H$100,MATCH(A4&B4&C4&D4&E4&F4,INDEX(Master!$A$1:$A$100&Master!$B$1:$B$100&Master!$C$1:$C$100& Master!$D$1:$D$100&Master!$E$1:$E$100&Master!$F$1:$F$100,0),0)),"")

Copy down.