comparing two excel files and saving the unique records in file 1

[irfanparbatani]

Hi friends

I have to compare two excel file 1 and file2

compare column N from file 1 to file 2, if duplicate records found in file 2 delete and paste unique records found In file 2 to a new worksheet with file 1 records and then unique records from file2 as a combined new workbook as history workbook.

Thanks

[irfanparbatani]

Hi Friends

I am using the below code, but it doesn't copy the unique value from file 2.

Please Login or Register  to view this content.

Please help.

[mongoose36]

Try this .. change the items in red

Please Login or Register  to view this content.

[irfanparbatani]

Try this .. change the items in red

Please Login or Register  to view this content.

Just testing it and I have a question, Why does it take so long when I run the macro, though it uses array, it should be faster, I have 3000 records only.

Thanks for the code.

[mongoose36]

The bottle neck occurs at the for loop that evaluates each cell.

Try this it uses 2 array loops instead.

Please Login or Register  to view this content.

[irfanparbatani]

The bottle neck occurs at the for loop that evaluates each cell.

Try this it uses 2 array loops instead.

Please Login or Register  to view this content.

=======================================

Hi
Thanks for the help.
I want to copy all the remaining values in file 2 to sheet 1 of file1 , how can I make changes to the code. also I want to delete the 7 days of top row data in file 1.

[irfanparbatani]

=======================================

Hi
Thanks for the help.
I want to copy all the remaining values in file 2 to sheet 1 of file1 , how can I make changes to the code. also I want to delete the 7 days of top row data in file 1.

=================================================

Can we copy the entire matching row from sheet 2 to unique values?

[mongoose36]

Try changing this...

Please Login or Register  to view this content.

to this...

Please Login or Register  to view this content.

[irfanparbatani]

Try changing this...

Please Login or Register  to view this content.

to this...

Please Login or Register  to view this content.

=====

Thank you for your help, much appreciated. but can we paste the values at the end of sheet 1 all the unique values. also need to delete 7 days of data from sheet 1 on the top.

[irfanparbatani]

Try changing this...

Please Login or Register  to view this content.

to this...

Please Login or Register  to view this content.

=====

Thank you for your help, much appreciated. but can we paste the values at the end of sheet 1 all the unique values. also need to delete 7 days of data from sheet 1 on the top.

[irfanparbatani]

Hi

This process still freezes my excel and i have to force close it. too slow if data is large in file 1. Will Filtering the data find help me to get the process faster.

[irfanparbatani]

The bottle neck occurs at the for loop that evaluates each cell.

Try this it uses 2 array loops instead.

Please Login or Register  to view this content.

===
The above code does not delete the value from ws2 when the match is found in sheet1. can you please check and respond me. sheet 1 is .csv extension and sheet2 is .xlsm extension, does that bother us from performing the above operation.
Thanks

[mongoose36]

Sorry for taking so long to get back to you. I don't see where you have changed the path or file name in the above code. These would need to be changed to your specific path and file names.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I don't think the fact the on workbook is csv will effect the operation of the code as long as excel is opening it.

In my tests everything worked as expected.

[irfanparbatani]

Hi Cpl

Thank you for replying. I changed the path and file 1is csv which will be compared and file 2 will be xlsm where the main data is pasted in one of the sheets. I tried a lot but it just gives me the same data which is in file 2. It does not delete rows found in sheet 2.
Please help and also the macro freezes up the excel if there are more than 2000 records.

[mongoose36]

Please post your workbook...

Also could you please clarify the logic ...

Hi friends

I have to compare two excel file 1 and file2

compare column N from file 1 to file 2, if duplicate records found in file 2 delete and paste unique records found In file 2 to a new worksheet with file 1 records and then unique records from file2 as a combined new workbook as history workbook.

Thanks

Eg...

1. Open File 2 (csv) from File 1 (xlsm)
2. Compare data in N Column of File 1 to column N of File 2
a. if match is found delete the entire row
3. Copy unique values left (if any) in File 2 to new worksheet titled "Unique Values" in File 1
4. It gets a little fuzzy here...

[irfanparbatani]

Please post your workbook...

Also could you please clarify the logic ...


Eg...

1. Open File 2 (csv) from File 1 (xlsm)
2. Compare data in N Column of File 1 to column N of File 2
a. if match is found delete the entire row
3. Copy unique values left (if any) in File 2 to new worksheet titled "Unique Values" in File 1
4. It gets a little fuzzy here...

====

Hi mate
Except point 3, just delete the row in file 2 and do not copy over the unique value from file 1.

Attaching workbook soon.

[irfanparbatani]

workbook attached

[irfanparbatani]

Also file 2 will open file 1 and file 1 is the compare dictionary, it would paste new 7 days data every time compare is performed.

[mongoose36]

Also file 2 will open file 1 and file 1 is the compare dictionary, it would paste new 7 days data every time compare is performed.

Ha Ha I've been thinking of it the exact opposite!