Compare each row in sheet1 to each row in sheet2 -no pair write in sheet3 - HARD

[NeewBie]

Hello guys!

I have an very big Excel file that has exactly same columns but dirosa. ent many rows. I need help with an VBA-script to compare each entire Eos in sheet1 with sheet2 If it dont finds an pair I want it to write the row in sheet3.
Its very important that the script starts in row 1 in sheet1 and then loop true whole Range in sheet2 to check If it fond an pair or not. If it cant find an match then I want it to write that row in sheet3.

Please need all help I can get!!

Using Excel 2010

Thnx in advance

 

[ace19852]

A different approach to your problem if to use helper columns in sheets 1 and 2. Identify the row in sheet1 that matches exactly a row in sheet 2 and then sort sheet1 and copy all the identified lines to sheet3.

For this example lets say you insert 2 columns in front of your data in sheet1 so we have a new column A and column B. That means the original column A is now column C.

Next in B1 concatenate all the cells in that row from C1 to ...... whatever is your last column

so C1 will look like =C1&D1&E1&F1........... etc

then copy this down the whole of column B

Now in Sheet2 just insert a new column A and repeat the process

in A1 concatenate all the cells in the row from B1 to ...... whatever is your last column

so B1 will look like =B1&C1&D1&E1........... etc

Now back to Sheet1

In A1 enter =IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,0)),"","Match")

then copy this down the whole of column A

Where the rows are identical "Match will appear in the cell.

You can then copy and paste special into sheet3 sort the data by column A and delete all lines without the Match flag etc

Hope you can follow all that - It's fairly easy once you have the idea.

However someone else will probably offer a VBA solution

Cheers

 

[NeewBie]

Thank you your answer but I really need an VBA-script to make this. Please need help!

Again thnx for ur feedback!!

A different approach to your problem if to use helper columns in sheets 1 and 2. Identify the row in sheet1 that matches exactly a row in sheet 2 and then sort sheet1 and copy all the identified lines to sheet3.

For this example lets say you insert 2 columns in front of your data in sheet1 so we have a new column A and column B. That means the original column A is now column C.

Next in B1 concatenate all the cells in that row from C1 to ...... whatever is your last column

so C1 will look like =C1&D1&E1&F1........... etc

then copy this down the whole of column B

Now in Sheet2 just insert a new column A and repeat the process

in A1 concatenate all the cells in the row from B1 to ...... whatever is your last column

so B1 will look like =B1&C1&D1&E1........... etc

Now back to Sheet1

In A1 enter =IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,0)),"","Match")

then copy this down the whole of column A

Where the rows are identical "Match will appear in the cell.

You can then copy and paste special into sheet3 sort the data by column A and delete all lines without the Match flag etc

Hope you can follow all that - It's fairly easy once you have the idea.

However someone else will probably offer a VBA solution

Cheers

 

[NeewBie]

Can someone hell me please or is this an impossible to create?

Thank you your answer but I really need an VBA-script to make this. Please need help!

Again thnx for ur feedback!!