Copy a Row if Cell Meets Conditions across two workbooks

[elapo21]

I would like to copy an entire row if Column T has a change in value (either 0 to 1 or 1 to 0) between two Workbooks (as each represents a different day of tracking). However, the Data in Rows A and B can shift day to day as more data is input.

Example Day 1 might be A2 - 1300 B2- McNutty and T2 -1 but Day 2 might be A2 1159 B2 - Hardt T2 - 1

Is this even possible?

[arlu1201]

I am not able to understand your question.

Why dont you attach with the help of a sample workbook?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[elapo21]

ex 8.6.xlsxex 8.7.xlsx

I would like to be able to see which IDs have a change in value from day to day as this WB is imported each day for tracking.

[arlu1201]

So if there is a change of value, what should be done?

[elapo21]

If there is a change in value I would like the row to be copied to a new sheet or section so that it stands out as changed.

Basically, we are trying to identify changed values so activities can be created or listed complete in another program.

[arlu1201]

Ok, so the macro will reside in an independent workbook right? since your files will change on a day to day basis?

So should the macro open up the file for the previous 2 days and then compare it and give the results in a sheet of the macro file?

[elapo21]

I would like it to do that. That way I would have it all isolated each day.

[elapo21]

Would it be possible to simplify this by having Retention and Retention -1 (Previous Days in one work book) and then base this off of: If A2=A2 but T2=/=T2 then copy the row to new sheet? I appreciate your help if you do happen to look.

[arlu1201]

I didnt understand what you mean by Retention.

Edit: Got it, having a look at the files.

---------- Post added at 06:35 PM ---------- Previous post was at 06:31 PM ----------

So if the id matches between both the files, even if 1 column in that row does not match to the other file, it should be reported right?

[elapo21]

What I have done is take the two work books at added the previous days main sheet to the next day. EX 8.9.xlsx

With the ultimate goal of tracking changes across a few different columns eventually, but currently focusing on Q. My thought process being if the information in A2 matches across both days but does not inQ2 then copy the row to the additional sheet.

I appreciate any assistance you may offer.

[arlu1201]

So just to confirm, if today is 8-9, you would be comparing data for 8-9 with 8-8?

Or would it be 8-8 compared with 8-7?

[elapo21]

It would change on a daily basis, but I was going to just import the data into the two existing sheets and always maintain the same sheet names. Tomorrow it would 8-10 and 8-9, etc.

[arlu1201]

Try this code. Put it in an independent workbook. It will prompt you to open today's file and then yesterday's file.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[elapo21]

Thank you so much, it works perfectly. However, how would I go about extending the range for every column change from M to U?

[arlu1201]

Even if one column from M to U does not match, that line should be copied right?

[elapo21]

I just ran it again and realized that it is actually just copying whatever is being shifted down on a day to day basis. My intention is to track changes within a Row based on criteria within a cell.

ie, If ID x has a change in any of the columns from M to U then it would be copied. Again, I'm not sure if this is something that can be accomplished as the data is constantly changing on a daily basis with additional IDs being added.

[arlu1201]

The code is checking each row in column A first. If it matches with the row below it, then it checks column Q. If its not the same, then the row is left intact.

I had inserted a temp column in R called "File", i guess if your last column is U, i will need to move it to V. Is this your final format - data till U?

[elapo21]

U is the final format. But perhaps I'm not communicating what the goal is. Obviously, each row represents a a single ID. The ID is static, but the SS exports by a name and can have items added to it in the middle or end. I really just need to know if any of the columns from M to U have a change in value on a day to day basis. Perhaps, checking column A is an unnecessary step?

[arlu1201]

Checking A is required to know that we are matching apples with apples.

[elapo21]

That is what I thought, I'm just not quite sure how to get around the issue of it adding the shifted accounts to it.

[arlu1201]

Dont worry. I will work this out for you.

[elapo21]

Thank you.

[elapo21]

I thought this might prove too improbable. I appreciate the effort you did give. Thanks.