Matching two worksheets

paulsolar

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
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
644
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
644
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
53,317
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
644
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
53,317
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
644
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
53,317
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,282
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top