473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

compare matching data in different sheets

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
3 1358
kadghar
1,295 Expert 1GB
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
boots
8
thanks, I will give that a shot.
Nov 13 '07 #3
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

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

Similar topics

0
by: Phil Powell | last post by:
/*-------------------------------------------------------------------------------------------------------------------------------- Parameters: $formField1: The name of the first array $formField2:...
8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
10
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in...
3
by: DONE1 | last post by:
I wrote a code as follows to compare 2 tables on diff worksheet. Each work sheet has about 6000 Rows. The code works but appears to Work very slow. Is there a more efficient way to do it. Sub...
1
by: Tbell | last post by:
Hello I admit I am somewhat of a beginner at this, but I am amazed as to why none of the examples and other help I have found have allowed me to get this working. I have two tables, table1 and...
2
by: lindabaldwin | last post by:
Hello everyone, I am fairly new to VBA. I have a worksheet in Excel, named "Data Sheet" from which I am trying to query data. This worksheet contains the following data: unit (column A), date...
0
by: grego9 | last post by:
I have a bit of visual basic code in an excel spreadsheet that I need some help with. I am attempting to search a file called TO Cancellations2.xls for counterparty names and for each counterparty...
14
by: moutoncito | last post by:
Hi all, I need some help with a VBA code. I have tried differents codes that i fund in the forum but they didn t match with what i m looking for. So i will explain what i have , what i need and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.