check for missing records and paste to sheet

[si3po]

hi all,

I have the following code:

     For iCounter = 9 To iLast
         Set rng1 = ws.Range("G9:G5000").Find(ws2.Range("G" & iCounter).Value)
         If rng1 Is Nothing Then
               ws2.Range("A" & iCounter & ":" & "K" & iCounter).Copy
               ws.Range("A" & ws.Range("A" & Application.Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
          End If
     Next iCounter

It is designed to take the value from the 'iCounter row' in Col.G of Sheet3 and search Col.G of Sheet2 for it. If it doesn't match, the whole row in sheet3 is then copied to the last row of sheet2.

this works reasonably well for rows where a duplicate in Col.G of sheet 3 (actually an order number) is not found, however if a duplicate (two rows with the same order number) exists in sheet3 - for example split orders, or multiple items/suppliers for one order - the code only 'sees' the first instance in Sheet2 and so won't copy any addtional rows for duplicate order numbers and simply moves the iCounter to the next row on Sheet3.

can anyone help with this please??

 

[DanteAmor]

Is there another identifier?
On sheet 3 you have 2 records with the same order number.
On sheet2 you only have one record.
You want to copy a record of sheet3 and paste it into sheet2. But which of the 2 records should be pasted on sheet2?

Example:
Sheet3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Order Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Order Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]123[/TD]
[/TR]
</tbody>[/TABLE]

Which record of sheet3 should I copy, record 2 or record 3?

 

[si3po]

Is there another identifier?
On sheet 3 you have 2 records with the same order number.
On sheet2 you only have one record.
You want to copy a record of sheet3 and paste it into sheet2. But which of the 2 records should be pasted on sheet2?
QUOTE]

Thanks Dante,

i'll see if i can explain with an example below.

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number
[/TD]
[TD]Item Name
[/TD]
[TD]Qty
[/TD]
[TD]Customer
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]Screw,brass 25mm
[/TD]
[TD]100
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]Hook, brass
[/TD]
[TD]25
[/TD]
[TD]Frank
[/TD]
[/TR]
[TR]
[TD]122
[/TD]
[TD]Nail, zinc 30mm
[/TD]
[TD]50
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]864
[/TD]
[TD]Nail, Steel 45mm
[/TD]
[TD]30
[/TD]
[TD]John
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number
[/TD]
[TD]Item Name
[/TD]
[TD]Qty
[/TD]
[TD]Customer
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]Screw,brass 25mm
[/TD]
[TD]100
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]Hook, brass
[/TD]
[TD]25
[/TD]
[TD]Frank
[/TD]
[/TR]
[TR]
[TD]122
[/TD]
[TD]Nail, zinc 30mm
[/TD]
[TD]50
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]864
[/TD]
[TD]Nail, Steel 45mm
[/TD]
[TD]30
[/TD]
[TD]John
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]Screw, brass 50mm
[/TD]
[TD]150
[/TD]
[TD]Bob
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]Cap, Dust
[/TD]
[TD]250
[/TD]
[TD]Frank
[/TD]
[/TR]
[TR]
[TD]456
[/TD]
[TD]Bit, Drill HSS 3mm
[/TD]
[TD]10
[/TD]
[TD]Frank
[/TD]
[/TR]
</tbody>[/TABLE]


In this example, Sheet3 has extra lines with an Order Number that already exists on Sheet2, but for different items. When updating Sheet2, these extra lines should also be copied over. Currently they are skipped as the order number already exists.

 

[DanteAmor]

You forgot to put in which column each data is found.

Order Number - column G
Item Name - column ?

 

[si3po]

You forgot to put in which column each data is found.

Order Number - column G
Item Name - column ?

Hi Dante,

I'm not sure that using the Item Name as an identifier will work, as it is plausible for me to have a single order number being fulfilled from multiple suppliers for the same item... a example would be that the customer places an order for, lets say 100 "Screws Bass, 50mm", but i only have 25 on my shelf stock. The remainder 75 will come into my stock from external source to satisfy this order.

To track whole order satisfaction and allow shipping of partial orders (e.g. the 25 from my stock) to the customer so that they are not kept waiting for an external supplier to deliver to me, the order is recorded as a single reference but with duplicates of that order for different quantites to the total requirement.

The only possible difference for each row in that instance (other than quantity, although this can be the same) would be what we call 'providers ref' - this is the Ext. Supplier order ref to us, but can often be blank until we get a notice from the suppliers. This value is plced into Col L.

 

[DanteAmor]

We can use 2 or more references, until we find unique records, it can be:

- Order Number - column G
- providers ref - Column L
- Item Name - column ?
- etc - Column ??

 

[si3po]

We can use 2 or more references, until we find unique records, it can be:

- Order Number - column G
- providers ref - Column L
- Item Name - column ?
- etc - Column ??

That might work, using the customer ID would help also i think. The fields are a follows

- Order Number - Column G
- providers ref - Column L
- Item Name - Column D
- Cust ID - Column J

 

[DanteAmor]

Try this, replace your code with the following.

  Dim c As Range, r As Range, f As Range, cell As String, exists As Boolean
  Set r = ws.Range("G9:G5000")
  For iCounter = 9 To iLast
    exists = False
    Set f = r.Find(ws2.Range("G" & iCounter).Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
        cell = f.Address
        Do
          If ws2.Range("G" & iCounter).Value = ws.Range("G" & f.Row).Value And _
             ws2.Range("L" & iCounter).Value = ws.Range("L" & f.Row).Value And _
             ws2.Range("D" & iCounter).Value = ws.Range("D" & f.Row).Value And _
             ws2.Range("J" & iCounter).Value = ws.Range("J" & f.Row).Value Then
             exists = True
             Exit Do
          End If
          Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
    End If
    If exists = False Then
      ws2.Range("A" & iCounter & ":" & "L" & iCounter).Copy
      ws.Range("A" & ws.Range("A" & Application.Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
    End If
  Next iCounter