trying to find solution to copy duplicated data from one sheet to

[winnie123]

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

 

[Joel]

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]

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]

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]

Hi Joel,

Thanks for getting back to me and the explanation on = sign.

I had noticed the typo with firstAddress

Unfotunately I still cant get it to work.

I have to go to work now so will try to work it out.

Just a thought I had a look back on some pervious posts and sometimes we used

Then
Found = True
Exit Do
End If

I will try to add this and see what I come up with.

Thanks Again
Winnie

 

[Joel]

You would only add the "exit Do" is you wanted to to return only the first
item found. I don't think you want to use it here. If the code isn't
working then some of the columns are not correct. Your description had the
Open sheet the order and line numbers in columns A & B respectively. The
shipped sheet had the same items in columns B & C. This may be the problem.

There are two types of problems you can have with this code.

1) No lines added to the Shipped sheet. This means the data isn't matching
between the two sheets.

2) Lines added to the shipped sheet but the wrong data is being put into the
Shipped sheets.


You may also have too many lines added to the shipped sheet and then we may
have to add additional filtering to the code. Lest get the code workig first
before we modify the algorithm.

 

[winnie123]

Hi Joel,

I modified your code slighty

from

OrderNum = .Range("A" & RowCount)
LineNum = .Range("B" & RowCount)

To

OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

As the order number and line number on "open" sheet are in cols C and D

I then changed
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

To

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

Just so that the correct column in "shipped" married up to the correct
column in "open"

For info the number of lines on the "shipped" sheet is different to the
number of lines on the "open" sheet, maybe this is the reason?

Currently there are 341 rows on the "shipped" and only 155 on "open"
including headers.

Thanks
Winnie

 

[Joel]

Is the code working or not working. Can't tell from the posting. The number
of rows should make a difference as long as the Order Numbers and Line Number
are the same. If the lastest version of the code so I can easily make
changes.

 

[winnie123]

Joel,

I have just run this code which took a good few minutes and what it seems to
have done is insert lines for all the shipped orders that marry up against
the order number and line number on the open sheet.

not sure why it was not doing that yesterday, but we will put that down to me

can we mod this so that it only inserts a line when there is more than one
instance of the order number and line number in the "shipped" sheet

Sorry for all the trouble I am causing but really appreciate your help

Thanks
Winnie

Sub LookupOrder()



Set bk = ThisWorkbook
Set Opensht = bk.Sheets("open")
Set ShipSht = bk.Sheets("shipped")

'work from last line to first line when inserting rows

With Opensht
Lrow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = Lrow
Do While RowCount >= 2
OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

With ShipSht


Set C = .Columns("B").Find(what:=OrderNum, LookIn:=xlValues,
lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'check if line number also matches
If LineNum = C.Offset(0, 1) Then
'Add New Row
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("P" & (RowCount + 1)) = C.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = C.Offset(0, 3)
'move column H
.Range("Q" & (RowCount + 1)) = C.Offset(0, 6)
'move column J
.Range("N" & (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
End Sub

 

[Joel]

If there are duplicates how do we know which order is already on the Open
Order and which isn't? Here is the modified code. I used sumproduct to get
the count of duplicates


Sub LookupOrder()

Set bk = ThisWorkbook
Set Opensht = bk.Sheets("open")
Set Shipsht = bk.Sheets("shipped")

'work from last line to first line when inserting rows

With Opensht
Lrow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = Lrow
Do While RowCount >= 2
OrderNum = .Range("C" & RowCount)
LineNum = .Range("D" & RowCount)

With Shipsht
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
Countformula = "sumproduct(" & _
"--(" & OrderNum & "=" & Shipsht.Name & "!B2:B" & LastRow & "),"
& _
"--(" & LineNum & "=" & Shipsht.Name & "!C2:C" & LastRow & "))"

Duplicates = Evaluate(Countformula)
If Duplicates > 1 Then
Set C = .Columns("B").Find(what:=OrderNum, _
LookIn:=xlValues, _
lookat:=xlWhole)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
'check if line number also matches
If LineNum = C.Offset(0, 1) Then
'Add New Row
With Opensht
.Rows(RowCount + 1).Insert
'move column D
.Range("P" & (RowCount + 1)) = C.Offset(0, 2)
'move column E
.Range("O" & (RowCount + 1)) = C.Offset(0, 3)
'move column H
.Range("Q" & (RowCount + 1)) = C.Offset(0, 6)
'move column J
.Range("N" & (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 If
End With
RowCount = RowCount - 1


Loop

End With
End Sub

 

[winnie123]

Got it

Thanks Joel for your help and patience, I know I can be trying at times.

Best Wishes

Winnie

 

[winnie123]

Joel,

You are correct with your statement "How do we know which order is already
on the Open Sheet"

Could your code be amended so that when the order and line have been
found/identified then look in column D of the "shipped" sheet and use the row
with the highest number. Aternatively col E has the date the order line was
shipped so could we use the latest date ?

Thanks
Winnie

 

[winnie123]

I have took a long winded approach but got it working as desired.