Matching two worksheets

[paulsolar]

Hi All

I'm hoping someone may be doing something like i need to do already, or at least point me in the right direction.

I have two worksheets in the same workbook. One is "Invoice List" this contains all the invoice numbers and values, column B (has a header) and has all the invoice numbers I'm trying to match. The second sheet "Remittance Import" has the invoice numbers in column C (has a header)

What I want to do is compare the invoice numbers in the remittance against the invoice list, if there is a match then put "Yes" in column F of the matching row of the Invoice list. (This way I know if the invoice has been paid)

If it was just one remittance I would do it with a formula, but i have 200+ remittances to check and the Invoice List sheet contains 20k plus rows. I have set it up so a remittance can only be checked once to save any confusion.

What I will do when the comparison for each remittance is completed is clear the Remittance Import sheet ready for the next entry.

Any help would be really appreciated.

cheers

Paul

 

[Fluff]

How about

Sub paulsolar()
   Dim Ary As Variant, Nary As Variant
   Dim Cl As Range
   Dim Dic As Object
   Dim r As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Invoice List")
      Ary = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value2
      Nary = .Range("F2:F" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   
   With Sheets("Remittance Import")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1)) Then Nary(r, 1) = "Yes"
   Next r
   Sheets("Invoice List").Range("F2").Resize(Nary).Value = Nary
End Sub

Best not mention 6 in a row

 

[paulsolar]

How about

Sub paulsolar()
   Dim Ary As Variant, Nary As Variant
   Dim Cl As Range
   Dim Dic As Object
   Dim r As Long
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Invoice List")
      Ary = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value2
      Nary = .Range("F2:F" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
  
   With Sheets("Remittance Import")
      For Each Cl In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
   End With
   For r = 1 To UBound(Ary)
      If Dic.Exists(Ary(r, 1)) Then Nary(r, 1) = "Yes"
   Next r
   Sheets("Invoice List").Range("F2").Resize(Nary).Value = Nary
End Sub

Best not mention 6 in a row

 

[paulsolar]

Hi Fluff

the way we are playing it will be 10 in a row soon.

I am getting a 1004 error, I have checked everything for typos ..... but can see anything wrong.

any ideas??

 

[Fluff]

Which line gives the error & what is the actual message.

 

[paulsolar]

Hi Fluff

Sorry for the delay, I had to reduce the size of the lists to test it

it's getting right to the end, it is finding matches OK

"Sheets("Invoice List").Range("F2").Resize(Nary).Value = Nary" is the line that's causing the problem

the error is "runtime error 1004 , application or object defined error

 

[Fluff]

That should be

   Sheets("Invoice List").Range("F2").Resize(UBound(Nary)).Value = Nary

 

[paulsolar]

Perfect Fluff, works like a dream, that has saved me an eon of work

I can get the job finished before this weekend now not next weekend. My accountant has posted some remittances by accident and I couldnt find them, so it was start from scratch

 

[Fluff]

You're welcome & thanks for the feedback.