Match based on a lookup and then flag

  • Thread starter Thread starter susan.m.beebe
  • Start date Start date
S

susan.m.beebe

I have 2 sheets. 1 sheet has 3 columns of data, the other has 3
also. 1 of the columns in each sheet contains the same information.
I need to do a lookup from sheet1 to sheet 2, based on the column in
common and then update the row in sheet 1 with the data from sheet 2.
It sounds like an easy vlookup, however, in each sheet there are
multiple occurences of the data in the column in common, but I need to
put the various pieces of data from sheet 2 into the rows in sheet 1.

For example:

sheet
1
Sheet2
Col1 Col2 Col3
Col1 Col2 Col3
A01 23456 123-1
292-1 ABC P000123
A02 23456 123-1
176-1 DEF P000124
A03 23456 123-1
292-1 GHI P000145
A04 23456 123-1
123-1 JKL P000654
B01 23456 176-1
123-1 MNO P000754
B02 12345 292-1
123-1 PQR P000275
B03 12345 292-1
292-1 STU P000198
B04 12345 292-1
123-1 VWX P000175


I need to be able to match sheet1.col3 to sheet2.col1 and have a
unique value from sheet2.col3 be used and not repeated.

The eventual output that I need is

Col1 and col3 from sheet 1 and col 3 from sheet 2 in a single sheet.

sheet 3 - Final result
Col1 Col2 Col3
A01 123-1 P000175
A02 123-1 P000654
A03 123-1 P000754
A04 123-1 P000275
B01 176-1 P000124
B02 292-1 P000145
B03 292-1 P000198
B04 292-1 P000123

Any suggestions?
 
Try...

Sub CombineData()
'Get data from sheets
Dim vD1, vD2, sAddr As String
vD1 = Sheets("Sheet1").UsedRange
vD2 = Sheets("Sheet2").UsedRange
sAddr = Sheets("Sheet1").UsedRange.Address

'Config data for output
Dim j As Long, k As Long
For j = LBound(vD1) To UBound(vD1)
vD1(j, 2) = vD1(j, 3): vD1(j, 3) = ""
Next 'j
For j = LBound(vD2) To UBound(vD2)
For k = LBound(vD1) To UBound(vD1)
If vD1(k, 2) = vD2(j, 1) _
And vD1(k, 3) = "" _
Then vD1(k, 3) = vD2(j, 3): Exit For
Next 'k
Next 'j

'Dump the data into Sheet3
Sheets("Sheet3").Range(sAddr) = vD1
End Sub
 
It works like a charm. However, ini trying to learn more from this, I
have some questions.


The line of code in the For next loop
What exactly is this doing?
vD1(j, 2) = vD1(j, 3): vD1(j, 3) = ""

And in the double loop through both sheet1 and sheet2, I guess I am
not following that either. The (k,2) and (j,1) refer to dimensions -
same as columns in this case on the 2 sheets??

For j = LBound(vD2) To UBound(vD2)
 
(e-mail address removed) formulated the question :
The line of code in the For next loop
What exactly is this doing?
vD1(j, 2) = vD1(j, 3): vD1(j, 3) = ""
Click to expand...

This line is preparing the output data in the original (Sheet1) array.
It puts col3 into col2 position because col2 data is not needed AND
that's where you want col3 data to be when it gets put in Sheet3. It
then inserts an empty string into col3 position so it can be filled
with data from Sheet2's array col3 data.
And in the double loop through both sheet1 and sheet2, I guess I am
not following that either. The (k,2) and (j,1) refer to dimensions -
same as columns in this case on the 2 sheets??
Click to expand...

This double loop iterates Sheet2's array (outer loop) to extract its
col3 data into Sheet1's array (inner loop) col3 position. To ensure
unique values only get inserted, the inner loop checks if Sheet1's
array, col2 data matches Sheet2's array col1 data. If it matches AND
only if Sheet1's array col3 contains an empty string will it put
Sheet2's col3 data into the array and exit the (inner) loop. Next
round, the outer loop moves to the next row of Sheet2's data and the
cycle for the Sheet1 array repeats. Since it will again find the same
match as the first loop, this is skipped because Sheet1's array col3 no
longer contains an empty string and so it continues to find the next
match.

As for the array dimensions, when we load Excel ranges into a variant
as done here it results in a 2D array. In the case of your sample data
this is an 8 row by 3 column array. So...

Dim vD1 'creates a variant where we put the data from Sheet1!$A1:$C8
This is now vD1(8, 3), a 2D 1-based array
This could also be dimmed vD1(1 To 8, 1 To 3)
The reason for 1-base is due to the way rows/cols are numbered.
(they start at 1)

This can be confusing at first because this is not the normal case for
working with conventional (0-based) arrays. The array created by Excel
ranges can be thought of in terms of R#C#, where the # sign is filled
with the row/col count in the range. Thus, element 1,1 of your 8x3
example contains the value in cell A1; 1,2 contains the value in B1,
and so on. So element 8,1 contains the value in A8; element 8,2
contains the value in B8, and so on.

HTH
 
Back
Top