Compare columns to figure out which rows were adjusted...

[abarbee314]

This feels like a "Track changes" sort of situation, but I can't nail down which way to go here (MATCH, VLOOKUP, etc.).

I essentially have four columns of data:
> Workbook 1
- an item reference number
- a detail amount

> Workbook 2
- an item reference number
- a detail amount

We looked at the sums of the 'detail amount' columns and realized that they were different between the two files. We are trying to figure out which particular rows are adjusted. Conditional formatting shows me unique/duplicate, but I need this in relation to the Detail Amount values. So, I need to evaluate the ItemReference cell and see if it's corresponding DetailAmount differs from one to the next.

We believe some of the dollar amounts were changed (I included sample data below). Note: there are, for some reason, items that are unique to each workbook, too.

Thanks, in advance, for your help! You guys always rock it out for me.

Aaron

*************************************************
[TABLE="width: 457"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ItemReference[/TD]
[TD]Detail Amt[/TD]
[TD]ItemReference2[/TD]
[TD]Detail Amt2[/TD]
[/TR]
[TR]
[TD="align: right"]170284[/TD]
[TD]($13.23)[/TD]
[TD="align: right"]216072[/TD]
[TD="align: right"]($54.00)[/TD]
[/TR]
[TR]
[TD="align: right"]171387[/TD]
[TD]($538.00)[/TD]
[TD="align: right"]223804[/TD]
[TD="align: right"]($2,190.00)[/TD]
[/TR]
[TR]
[TD="align: right"]182177[/TD]
[TD]($4.11)[/TD]
[TD="align: right"]223805[/TD]
[TD="align: right"]($5,370.00)[/TD]
[/TR]
[TR]
[TD="align: right"]184476[/TD]
[TD]($1,050.00)[/TD]
[TD="align: right"]223806[/TD]
[TD="align: right"]($3,610.00)[/TD]
[/TR]
[TR]
[TD="align: right"]216072[/TD]
[TD]($54.00)[/TD]
[TD="align: right"]223807[/TD]
[TD="align: right"]($1,590.00)[/TD]
[/TR]
[TR]
[TD="align: right"]223804[/TD]
[TD]($2,190.00)[/TD]
[TD="align: right"]223808[/TD]
[TD="align: right"]($5,430.00)[/TD]
[/TR]
[TR]
[TD="align: right"]223805[/TD]
[TD]($5,370.00)[/TD]
[TD="align: right"]223809[/TD]
[TD="align: right"]($1,320.00)[/TD]
[/TR]
[TR]
[TD="align: right"]223806[/TD]
[TD]($3,610.00)[/TD]
[TD="align: right"]224004[/TD]
[TD="align: right"]($622.74)[/TD]
[/TR]
[TR]
[TD="align: right"]223807[/TD]
[TD]($1,590.00)[/TD]
[TD="align: right"]224132[/TD]
[TD="align: right"]($3,390.00)[/TD]
[/TR]
[TR]
[TD="align: right"]223808[/TD]
[TD]($5,430.00)[/TD]
[TD="align: right"]224133[/TD]
[TD="align: right"]($1,770.00)[/TD]
[/TR]
[TR]
[TD="align: right"]223809[/TD]
[TD]($1,320.00)[/TD]
[TD="align: right"]224134[/TD]
[TD="align: right"]($4,550.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224004[/TD]
[TD]($622.74)[/TD]
[TD="align: right"]224135[/TD]
[TD="align: right"]($2,480.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224132[/TD]
[TD]($3,390.00)[/TD]
[TD="align: right"]224136[/TD]
[TD="align: right"]($8,230.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224133[/TD]
[TD]($1,770.00)[/TD]
[TD="align: right"]224137[/TD]
[TD="align: right"]($870.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224134[/TD]
[TD]($4,550.00)[/TD]
[TD="align: right"]224138[/TD]
[TD="align: right"]($4,395.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224135[/TD]
[TD]($2,480.00)[/TD]
[TD="align: right"]224139[/TD]
[TD="align: right"]($8,350.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224136[/TD]
[TD]($8,230.00)[/TD]
[TD="align: right"]224140[/TD]
[TD="align: right"]($4,850.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224137[/TD]
[TD]($870.00)[/TD]
[TD="align: right"]224141[/TD]
[TD="align: right"]($2,760.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224138[/TD]
[TD]($4,395.00)[/TD]
[TD="align: right"]224142[/TD]
[TD="align: right"]($5,510.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224139[/TD]
[TD]($8,350.00)[/TD]
[TD="align: right"]224143[/TD]
[TD="align: right"]($1,750.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224140[/TD]
[TD]($4,850.00)[/TD]
[TD="align: right"]232660[/TD]
[TD="align: right"]($330.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224141[/TD]
[TD]($2,760.00)[/TD]
[TD="align: right"]236294[/TD]
[TD="align: right"]($335.00)[/TD]
[/TR]
[TR]
[TD="align: right"]224142[/TD]
[TD]($5,510.00)[/TD]
[TD="align: right"]236295[/TD]
[TD="align: right"]($50.00)[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

A simple method would be to copy the itemreference columns, paste somewhere else one above the other, sort, remove the duplicates (through the Excel menu or advanced filter), then lookup from each column so you'll see all the changes concisely.

 

[abarbee314]

So, I was definitely over-thinking this one. I ran a VLOOKUP on the Item Reference columns and extracted the Detail Amt values. Then, I simply subtracted that result from the first set. So, I was left with N/A for unique items and was able to quickly find the two items that had been changed.