Merge/Join 2 Worksheet Data on Single Column

[t3chCh!c]

I need help automating a weekly process by creating a VBA macro. The process calls for merging the data of two worksheets into one NEW worksheet based on values in a single column. The two worksheets will have a varying number of tuples for every run, but the data format/layout will be the same. There are a few conditions: records/tuples must match on a single shared column (CODE in the example). If there is no match, the tuple will be included in the new sheet without any matching data to fill up the remaining columns. That would be the easiest way to spot the records that did not have a match.

The data layout is below:

WORKSHEET 1
[Name] [Code]

WORKSHEET 2
[Time] [Code] [Amount]

NOTE: Shared column is CODE.


The real data has been simplified to these few columns to easily express the concept. Actual data is around 25 columns. Attached is a tab delimited example data file (forum_exampleData.txt).

Thanks in advance for any contribution!
Mary

[bhofsetz]

What data from the two worksheets do you want to merge? What columns from your example do you want to end up on your output sheet? Are the rows always in the same order on the two sheets? You example data contains the same code in the same order on the two sheets with none having 'missing' values.
Clarification would help answer your question.

[t3chCh!c]

The data in the two spreadsheets will always be in the same order. This should help in coding the merge process. Any rows with a missing AUTHCODE will be ignored and not included in the final output. It is key that AUTHCODE be matched ... this should be similar to a unique primary key in a db.

All of the fields from the two worksheets will be included on the new worksheet. So, the resulting spreadsheet would contain the following fields:

NAME
AUTHCODE
TRANSACTION TIME
RESPONSE
AMOUNT
COMMENT1

Thanks!

[bhofsetz]

Give this code a try.
If you have more than 100 rows of data then you will have to enlarge the size of your arrays (or make it dynamic and count the rows).
Also it is set up for 5 columns on Sheet1 and 2 on Sheet 2. Redimension the arrays appropriately for the actual number of columns on eash sheet.

I'm not sure if this is the most eloquent bit of code but it works. Perhaps some of the guru's here can give some suggestions for improvements.

Hope this helps

Option Explicit
Dim Sht1(100, 1 To 5) As String, Sht2(100, 1 To 2) As String
Dim x As Integer, y As Integer, z As Integer

Sub LoadArrays()
Sheets(1).Select
x = 1
y = 0
Do Until Cells(x, 1) = ""
If Cells(x, 2) = "" Then GoTo SkipSht1
Sht1(y, 1) = Cells(x, 1)
Sht1(y, 2) = Cells(x, 2)
Sht1(y, 3) = Cells(x, 3)
Sht1(y, 4) = Cells(x, 4)
Sht1(y, 5) = Cells(x, 5)
y = y + 1
SkipSht1:
x = x + 1
Loop
Sheets(2).Select
x = 1
y = 0
Do Until Cells(x, 1) = ""
If Cells(x, 2) = "" Then GoTo SkipSht2
Sht2(z, 1) = Cells(x, 1)
Sht2(z, 2) = Cells(x, 2)
z = z + 1
SkipSht2:
x = x + 1
Loop
End Sub

Sub Compare_and_Unload()
Sheets(3).Select
x = 0
z = 0
Do Until Sht2(z, 1) = ""
y = 0
Do Until Sht1(y, 1) = ""
If Sht1(y, 2) = Sht2(z, 2) Then
x = x + 1
Cells(x, 1) = Sht2(z, 1)
Cells(x, 2) = Sht2(z, 2)
Cells(x, 3) = Sht1(y, 1)
Cells(x, 4) = Sht1(y, 3)
Cells(x, 5) = Sht1(y, 4)
Cells(x, 6) = Sht1(y, 5)
y = y + 1
z = z + 1
Else: y = y + 1
End If
Loop
Loop
End Sub