Matching two worksheets

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
636
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Aug 21, 2013
Messages
636
How about
VBA Code:
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

Well-known Member
Joined
Aug 21, 2013
Messages
636
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
636
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That should be
VBA Code:
   Sheets("Invoice List").Range("F2").Resize(UBound(Nary)).Value = Nary
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
636
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 :oops:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,477
Members
412,595
Latest member
slim313
Top