Find Differences Between Worksheets and Export to Third Worksheet

[samwell47]

Hello forums,

I have a dream of an Excel macro or tool that would make my job infinitely easier, but attempting to cobble it together on my own from Google and forum posts has not proved fruitful.

One of my job duties is to take reports that are exported nightly and compare them to determine what, if anything has changed. My predecessor did this manually. My current method involves pasting the data side by side, and using conditional formatting and highlighting to identify changes - a little more automated, but... Since there are additions and deletions from one day to the next, I have to go through the report and manually add spaces in one side or the other when an entry has been added or deleted, so the conditional formatting can check the rows 1:1 (my "code" is just three columns of =IF(this=that,"Y","N") statements).

This, obviously, sucks to do by hand and wastes a lot of time, and I know there's got to be an easier way.

My data looks like this (but each sheet is hundreds of rows long)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD]101[/TD]
[TD]1001[/TD]
[TD]John[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD]101[/TD]
[TD]1002[/TD]
[TD]Jane[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]101[/TD]
[TD]1003[/TD]
[TD]Alex[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]201L[/TD]
[TD]1004[/TD]
[TD]Ruth[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD]101[/TD]
[TD]1002[/TD]
[TD]Ford[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]101[/TD]
[TD]1003[/TD]
[TD]Alex[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]105[/TD]
[TD]1005[/TD]
[TD]Beth[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]201L[/TD]
[TD]1004[/TD]
[TD]Ruth[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]HIST[/TD]
[TD]302[/TD]
[TD]1006[/TD]
[TD]Quentin[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]

I'd love to find a piece of code or tool that could output this from an input of two sheets:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD]101[/TD]
[TD]1001[/TD]
[TD]John[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD]101[/TD]
[TD]1002[/TD]
[TD]Jane > Ford[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]105[/TD]
[TD]1005[/TD]
[TD]Beth[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]HIST[/TD]
[TD]302[/TD]
[TD]1006[/TD]
[TD]Quentin[/TD]
[TD]45[/TD]
[/TR]
</tbody>[/TABLE]

Where the red row appears in Sheet 1 but not 2 (deletion), the green rows appear in Sheet 2 but not Sheet 1 (addition), and the row with a value changed in column D or E is pasted in with the change in value noted.

Instead of me having to do this and waste half an hour every day:

Any assistance would be infinitely appreciated. Thank you!

 

[oldbrewer]

[TABLE="width: 1049"]
<colgroup><col span="3"><col><col span="4"><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1001[/TD]
[TD]John[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART1011001John25[/TD]
[TD]deleted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1002[/TD]
[TD]Jane[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART1011002Jane10[/TD]
[TD]deleted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1003[/TD]
[TD]Alex[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL1011003Alex30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]201L[/TD]
[TD="align: right"]1004[/TD]
[TD]Ruth[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL201L1004Ruth5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1002[/TD]
[TD]Ford[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART1011002Ford10[/TD]
[TD]added[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1003[/TD]
[TD]Alex[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL1011003Alex30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]1005[/TD]
[TD]Beth[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL1051005Beth20[/TD]
[TD]added[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD]201L[/TD]
[TD="align: right"]1004[/TD]
[TD]Ruth[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL201L1004Ruth5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HIST[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]1006[/TD]
[TD]Quentin[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HIST3021006Quentin45[/TD]
[TD]added[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1001[/TD]
[TD]John[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1002[/TD]
[TD]Jane > Ford[/TD]
[TD="align: right"]15[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BIOL[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]1005[/TD]
[TD]Beth[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HIST[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]1006[/TD]
[TD]Quentin[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]by concatenating first[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then checking top set against bottom set to find deletions[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]then checking bottom set against top set to find additions[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I do not understand row marked #####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]very easy to conditionally format top table red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]if column J = deleted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]similarly the bottom table green for new rows[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I think your bottom table is wrong[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]and it should be[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1001[/TD]
[TD]John[/TD]
[TD="align: right"]25[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1002[/TD]
[TD]Jane[/TD]
[TD="align: right"]10[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ART[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]1002[/TD]
[TD]Ford[/TD]
[TD="align: right"]10[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BIOL[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]1005[/TD]
[TD]Beth[/TD]
[TD="align: right"]20[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HIST[/TD]
[TD="align: right"]302[/TD]
[TD="align: right"]1006[/TD]
[TD]Quentin[/TD]
[TD="align: right"]45[/TD]
[TD]green[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[samwell47]

The bottom table is not "wrong." I guess I didn't make it clear what I needed (but that's a rude way to say you didn't get it, you know). The line with ID "1002" needs to indicate that it changed from Jane to Ford, not be repeated twice in the output as both an addition and deletion. I suppose I can run some secondary check on that output. Like, looking for duplicated of ID number and then outputting the change...

I guess I'll try that concatenation. I suppose it doesn't matter how kludgy the code of my macro looks as long as it gets me the right results in the end, right? I guess I'll mess with this for a while and see if I can get any further.

It''s really frustrating, though, to come in to a help forum and be told that my example of what I'm looking for is "wrong" just because you don't understand. I don't think I'll be returning to this particular website if I need more help on this topic.

 

[oldbrewer]

It is very hard to communicate by Email or forum. I clearly did not understand what you wanted. It is hard for a person who understands the scenario perfectly to give all the right info in their initial post. This is a very good forum with hundreds of knowledgeable people. I did not mean to tell you that your data was wrong, if we were talking face to face I would have asked "how is that value there derived". I sincerely hope you find a solution quickly.