Duplicate row entries that change

[ajlfw]

So let me try to explain my problem. My company does a report each month for what we need to ship and it comes out in an excel document. Something like:

date Job# Qty Part#
8/1/11 5555 1 Bacon
8/3/11 5556 2 Cheese
8/3/11 5555 3 Candy
8/5/11 5687 4 Candy

So it's my job to go thru and find out if everything is on schedule and if we need to hurry production of certain jobs. My problem arises when we do the report for the next month. Quite often, customers will change the delivery date, or quantity of the items they want, as well as new orders comming in. It becomes quite a chore to wade thru the whole report again to weed out the duplicates and only show me the new ones I need to be concerned about. I've tried using Vlookup and some of the other examples I've found online for comparing the differences between two Workbooks, and nothing seems to work. Mostly because i need to do it on a row by row basis I think. I have multiple deliveries of the same part and job numbers, and I don't have a way to uniquely identify each shipment. I even toyed around with trying to import the data to access thinking a database would maybe remove any duplicated information but I don't know it very well. I'd prefer to stay with excel if at all possible as that's the format in which I receive the report. Any help would be appreciated. Thanks

 

[Damon Ostrander]

Hi ajlfw,

It is not clear from your problem description how you can identify duplicates, even manually, if there is no way to uniquely identify each shipment. However, I am sure that if you are able to do this manually, then it would not be difficult to write a macro that would automate it. If you could describe how you currently identify duplicates, I would be happy to at the very least let you know what is involved in automating it.

Damon

 

[ajlfw]

Basically I sort it by date, then job number, and compare the lines one by one for any changes in qty. The job may move dates as well. New ones are added and old ones are removed. The job and customer po fields are fairly constant, but sometimes u have multiple shipments on the same job number, and multiple parts on the customer po. Basically, I need to compare the whole row of values against a whole different row of values in the new workbook. If any of the columns change, I need it to tell me. So end result is it tells me what the differences are. I tried using the compare feature, but since the top lines ship, it sees everything as different since it shifts everything up on the next report.

 

[markmzz]

If I understand correctly what you want, then this can help you:

Initially, use the advanced filter feature of Excel to get the unique record of the columns Job# and Part# - Date (Guide/menu)/Filter/Advanced filter. Look at this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">date</TD><TD style="TEXT-ALIGN: center">Job#</TD><TD style="TEXT-ALIGN: center">Qty</TD><TD style="TEXT-ALIGN: center">Part#</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Job#</TD><TD style="TEXT-ALIGN: center">Part#</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">8/1/2011</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Bacon</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">Bacon</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">8/3/2011</TD><TD style="TEXT-ALIGN: center">5556</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Cheese</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">5556</TD><TD style="TEXT-ALIGN: center">Cheese</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">8/3/2011</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">Candy</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">8/5/2011</TD><TD style="TEXT-ALIGN: center">5687</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">5687</TD><TD style="TEXT-ALIGN: center">Candy</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">8/7/2011</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Bacon</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">8/9/2011</TD><TD style="TEXT-ALIGN: center">5556</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Cheese</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">8/11/2011</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">8/13/2011</TD><TD style="TEXT-ALIGN: center">5687</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD></TR></TBODY></TABLE>Sheet4

Then use the formulas (array formulas - use Ctrl+Shift+Enter and not only Enter) below:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Job#</TD><TD style="TEXT-ALIGN: center">Part#</TD><TD style="TEXT-ALIGN: center">date</TD><TD style="TEXT-ALIGN: center">Qty</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">Bacon</TD><TD style="TEXT-ALIGN: center">8/7/2011</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">5556</TD><TD style="TEXT-ALIGN: center">Cheese</TD><TD style="TEXT-ALIGN: center">8/9/2011</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">5555</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: center">8/11/2011</TD><TD style="TEXT-ALIGN: center">7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">5687</TD><TD style="TEXT-ALIGN: center">Candy</TD><TD style="TEXT-ALIGN: center">8/13/2011</TD><TD style="TEXT-ALIGN: center">8</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD></TR></TBODY></TABLE>Sheet4


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I2</TH><TD style="TEXT-ALIGN: left">{=MAX(IF(G2=B$2:B$9,IF(H2=D$2:D$9,A$2:A$9,0)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>J2</TH><TD style="TEXT-ALIGN: left">{=INDEX(C$2:C$9,MATCH(G2&H2&I2,B$2:B$9&D$2:D$9&A$2:A$9,0))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

I hope this help you.

Markmzz