|
trying to find solution to copy duplicated data from one sheet to
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 |
trying to find solution to copy duplicated data from one sheet to
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 |
trying to find solution to copy duplicated data from one sheet
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 |
trying to find solution to copy duplicated data from one sheet
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 |
trying to find solution to copy duplicated data from one sheet
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" wrote: 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 |
trying to find solution to copy duplicated data from one sheet
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" wrote: 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" wrote: 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 |
trying to find solution to copy duplicated data from one sheet
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 "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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) |
trying to find solution to copy duplicated data from one sheet
I have took a long winded approach but got it working as desired.
"winnie123" wrote: 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 "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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: |
| All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com