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)

A	B	C	D	E
ART	101	1001	John	25
ART	101	1002	Jane	10
BIOL	101	1003	Alex	30
BIOL	201L	1004	Ruth	5

<tbody>
</tbody>

A	B	C	D	E
ART	101	1002	Ford	10
BIOL	101	1003	Alex	30
BIOL	105	1005	Beth	20
BIOL	201L	1004	Ruth	5
HIST	302	1006	Quentin	45

<tbody>
</tbody>

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

A	B	C	D	E
ART	101	1001	John	25
ART	101	1002	Jane > Ford	15
BIOL	105	1005	Beth	20
HIST	302	1006	Quentin	45

<tbody>
</tbody>

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]

ART	101	1001	John	25				ART1011001John25	deleted
ART	101	1002	Jane	10				ART1011002Jane10	deleted
BIOL	101	1003	Alex	30				BIOL1011003Alex30
BIOL	201L	1004	Ruth	5				BIOL201L1004Ruth5
ART	101	1002	Ford	10				ART1011002Ford10	added
BIOL	101	1003	Alex	30				BIOL1011003Alex30
BIOL	105	1005	Beth	20				BIOL1051005Beth20	added
BIOL	201L	1004	Ruth	5				BIOL201L1004Ruth5
HIST	302	1006	Quentin	45				HIST3021006Quentin45	added
ART	101	1001	John	25
ART	101	1002	Jane > Ford	15	#####
BIOL	105	1005	Beth	20
HIST	302	1006	Quentin	45				by concatenating first
								then checking top set against bottom set to find deletions
								then checking bottom set against top set to find additions
								I do not understand row marked #####
								very easy to conditionally format top table red
								if column J = deleted
								similarly the bottom table green for new rows
								I think your bottom table is wrong
								and it should be
						ART	101	1001	John	25	red
						ART	101	1002	Jane	10	red
						ART	101	1002	Ford	10	green
						BIOL	105	1005	Beth	20	green
						HIST	302	1006	Quentin	45	green

<colgroup><col span="3"><col><col span="4"><col><col span="4"></colgroup><tbody>
</tbody>

 

[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.