Find differences in two sets of pasted data and parse this data

Was that a typo in C12 where you have NKKR-91-EX-00002, but in C6 which appears to be the same record you have NKKRR-91-EX-00002, i.e. an extra 'R'

Will all the morning records be there in the afternoon?

Will the order of records always be the same, albeit there may be additional ones in the afternoon.

Originally Posted by cwoodgate

Hi in order to give you more assistance, the only real key fields we are focusing on are the "amount " in column J as well as the "docket balance" in column K. if one or both those values change from AM to PM, I want the remainder of the row associated with the changed values as well as how much the fields changed.

But that doesn't explain how any function can determine that the row 12 is the same as the row 6 record and row 5 the same as row 11.

There needs to be some key that connects the two records. It seemed reasonable to assume that the ABC Doc No would be that key field so I'm surprised that it isn't. But since it isn't how should any two records be paired in order to deduce any difference?

OK,

However the original question is still relevant. i.e.

Was that a typo in C12 where you have NKKR-91-EX-00002, but in C6 which appears to be the same record you have NKKRR-91-EX-00002, i.e. an extra 'R' in the doc number.

I can only presume it's a typo since you want to output the difference of 7.5 for the doc balance between these two rows.

Hi,

The attached contains the following macro.
I know you said you'd prefer not to have a macro but to be honest it's far simpler than creating formulae to work out where there are differences and only list the differences.
If you don't want the macro then personally I'd just copy the O1:R1 formulae down sheet2 and to see the differences only just filter sheet2 for "Y" in column R

Please Login or Register  to view this content.

Add the afternoon data to Sheet2 A:N. If you're copying from a file you you might consider building that step into this macro.

Click the blue button to run the macro which will automatically add the formulae in columns O:R and populate sheet3 with the differences which will be columns P:Q

If I understand the Q. then the simplest non macro way would be to filter sheet 2 for non blanks in either columns P or Q.

Using the macros then I've added three Option Buttons. Clicking one of them will filter sheet3 for different choices.

See attached.

Note that on sheet 3 A1:C2 are the filter values.
A1:B2 is named 'Crit1'
B1:B2 is named 'Crit'
B2:C2 is named 'Crit2'

When you click one of the option buttons that updates sheet2 cell A1 with either a 1, 2 or 3.

Where previously the macro that filtered the data used 'Crit' I've now changed it so that it recognises from A1 on Sheet1 which option button has been clicked and it then filters the data accordingly. The macro is now

Please Login or Register  to view this content.

Hi,

See attached

You just need to select all three option buttons (hold the Ctrl key down when you select them) and Cut and Paste them to sheet 3.
Then Cut and Paste the Sheet2 A1 cell (named 'FilterChoice') to say D1 on Sheet3 and make sure the option button Properties point to the name 'FilterChoice' rather than A1

You might want to drag the option buttons down a bit so that you can hide rows 1:2 on sheet 3 and widen row 4 so that the option buttons are all together, or space them out horizontally

Richard,
Hello, seems I have a small hiccup.
I am noticing that my data when pasted is overlapping formulas in another column.. This overlap results in data spreading out into the next column, "O" or the Morning row, where formulas are-this makes the macro not function well. How may I move the functional area of the macro so that it is not referencing column "O"? I tried to insert a column in column O to give the the pasted data some room to breathe, however the macro fails.

Clare

Hi Clare,

I don't understand.

Would you upload the workbook you're using.
Make sure you show a starting position sheet and tell me exactly what you're doing and show another sheet which shows what you end up with.

It's sufficient to post here. I'll pick it up when you do.