You can move data into a cell either with copy and paste, or just using an
equal sign.

I found the mistakes. simple typo errors

1)
from
RowCount = RowCount = 1
to
RowCount = RowCount - 1

2)
from
Loop While Not c Is Nothing And _
c.Address < FirstAddress
to
Loop While Not c Is Nothing And _
c.Address < FirstAddr

"winnie123" wrote:

Hi Joel,

First, thankyou for responding

I have tried your code and unfortunately I cant seem to get it to work. it
does not seem to make any changes on the "open" sheet.

I can understand the logic you have used, but where in the code is the copy
and paste. Maybe I dont understand as much as I thought.

With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("N" & (RowCount + 1)) = _
c.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = _
c.Offset(0, 3)
'move column H
.Range("P" & (RowCount + 1)) = _
c.Offset(0, 6)
'move column J
.Range("Q" & (RowCount + 1)) = _
c.Offset(0, 8)
End With

Sorry to be a pain.

Thanks
Winnie



"Joel" wrote:

You probably can do everyting in a macro. I started to open the Order book
but realized I didn't have to and commented out the associated code that
wasn't needed.

Because you need to add rows the best way is to start at the last row of the
open workbook and moved toward the first row. You can match the order number
and line number in VBA byt using Find and findnext as I did below. the code
will find all multiple duplicates. I didn't test the code but it should get
you started.


Sub LookupOrder()

Set bk = ThisWorkbook
Set Opensht = bk.Sheets("Open")
Set ShipSht = bk.Sheets("Shipped")

'Set OrderBk = Workbooks.Open( _
Filename:="Barcrest Daily Update1.xls")
'Set OrderSht = OrderBk.Sheets("Order")

'work from last line to first line when inserting rows

With Opensht
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = LastRow
Do While RowCount = 2
OrderNum = .Range("A" & RowCount)
LineNum = .Range("B" & RowCount)

With ShipSht
Set c = .Columns("B").Find(what:=OrderNum, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
'check if line number also mattches
If LineNum = c.Offset(0, 1) Then
'add new row
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("N" & (RowCount + 1)) = _
c.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = _
c.Offset(0, 3)
'move column H
.Range("P" & (RowCount + 1)) = _
c.Offset(0, 6)
'move column J
.Range("Q" & (RowCount + 1)) = _
c.Offset(0, 8)
End With
End If
Set c = .Columns("B").FindNext(after:=c)
Loop While Not c Is Nothing And _
c.Address < FirstAddress

End If

End With
RowCount = RowCount = 1
Loop

End With

'bk.Close savechanges:=False

End Sub


"winnie123" wrote:

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