Hello all,

I have created a file in excel 2003, which looks at orders that have been
shipped.

I am struggling to find a fix when there is a partial shipment made so the
only way I can think of is to use code rather than a formula.

Within the file I have a sheet named open and a sheet named shipped

On the "open" sheet there are col A - AI
Cols N-Q contain a Match and Index formula which looks up the value from
"Shipped" sheet, all is fine until there has been a partial shipment as it
just shows the first found match.

The formula I am using is, this is the example for Col N which looks up the
despatch note number, col O look ups the invoice date, col P looks up the
Invoice number and col Q looks up the qty

=IF(ISNUMBER(MATCH(1,(C2='Barcrest Daily Update1.xls'!Order)*(D2='Barcrest
Daily Update1.xls'!Line),0)),INDEX('Barcrest Daily
Update1.xls'!DespatchNote,MATCH(1,(C2='Barcrest Daily
Update1.xls'!Order)*(D2='Barcrest Daily Update1.xls'!Line),0)),"")

The "shipped" sheet runs from A-N

Col B has the order number
Col C has the line number
Col D has the invoice number
Col E has the invoice date
Col H has the qty
Col J has the Despatch note number

I can identify the duplicate records on the "shipped" sheet by adding col O
and entering the formula =B2&C2 this give me the order number and line number
combined
Then in Col P entering =IF(COUNTIF($O$2:O2,O2)1,"Duplicate","Unique")

is it possible to then insert any records which have "duplicate" from the
"shipped" sheet? Col D E H J to the "open" Sheet col N O P Q

I would new a new row to be inserted ideally underneath the first instance
with all the remaining columns being copied from the row above.

I would thus end up with something like this, only provide sample of col

Order Line DespatchNote InvoiceDate Invoice Qty
123 1 546 01-jan-09 678 3
123 1 578 04-jan-09 702 2 this would be the
inserted line
156 1 900 10-feb-09 101 50


Hope I have explained well enough for suggestions
Thanks
Winnie