By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,856 Members | 1,986 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,856 IT Pros & Developers. It's quick & easy.

compare matching data in different sheets

P: 8
I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.
Nov 12 '07 #1
Share this Question
Share on Google+
3 Replies


kadghar
Expert 100+
P: 1,295
I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.
Hi,
You can assign a range to a variant, so you can work it with FOR or DO. Try to get the info you want into an array and after that send it to Excel. If you use Excel during the process, it'll slow things down.

Something like this should do.

Expand|Select|Wrap|Line Numbers
  1. sub matchData()
  2. dim a
  3. dim b
  4. dim c() as string
  5. dim i as long
  6. dim j as long
  7. dim k as long
  8. With Worksheets(1)
  9.     a = range(.cells(1,1), .cells(1,1).end(-4121).end(-4161))
  10. End With
  11. With Workseets(2)
  12.     b = range(.cells(1,4), .cells(1,4).end(-4121))
  13. End With
  14. ReDim Preserve c (1 to Ubound(a,2)-3)
  15. For i = 1 To Ubound(a)
  16.     j = 1
  17.     Do
  18.         If b(j,1) = a(i,3) Then 
  19.             For k = 1 To Ubound(c)
  20.                 c(k) = a(i,k+3)
  21.             Next
  22.             With Worksheets(2)
  23.                 Range(.Cells(j,5),.Cells(j,4+ubound(c)) = c
  24.             End With
  25.             Exit Do
  26.         End If
  27.         j = j + 1
  28.      Loop Until j > Ubound(b)
  29. Next
  30. End Sub

HTH
Nov 12 '07 #2

P: 8
thanks, I will give that a shot.
Nov 13 '07 #3

P: 44
If you are having really much, I'ld assign the cell content into variables and let it mtch the values of them in the backround. Should be a little faster if you need it more often
Nov 13 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.