How to take data from one excel table and add it to matching rows in a different table?

Question

I have two excel tables, Table A has 4 columns, Table B has 13. Each of the 4 columns in Table A can be found in Table B. They are composed of count data from censuses. During the censuses, people counted the species they encountered and gave a value but they did not write down when they did not encounter a species. I added in 0's for years and locations where species were not found using pivot charts/macros. But now I have my Table A that includes the 0 values but it's missing all the extra data from Table B. The tables look something like this (simplified):

Table A

species    location    year    value
Mango      A           2001    2
Mango      A           2002    3
Mango      A           2003    1
Avocado    A           2001    1
Avocado    A           2002    0
Avocado    A           2003    0
Mango      B           2001    0
Mango      B           2002    2
Mango      B           2003    20
Avocado    B           2001    25
Avocado    B           2002    80
Avocado    B           2003    0


Table B

species    location    year    value   month   day    group    uploaded?
Mango      A           2001    2       12      1      X        No     
Mango      A           2002    3       12      5      X        Yes      
Mango      A           2003    1       12      3      X        No
Avocado    A           2001    1       12      1      X        No
Mango      B           2002    2       12      6      Y        No
Mango      B           2003    20      12      7      Y        No
Avocado    B           2001    25      12      4      Y        No
Avocado    B           2002    80      12      6      Y        No

You can see that Table B contains all the rows in Table A that have values above 0 but does not contain the rows with values of 0. Every year/location combo in Table B has the same data for every other column other than species and value.

Is there a way to take the data from Table B and put it into the appropriate rows in Table A? I would like it to work so that every location/year combo in Table B will be transported into every row (including the rows with 0) in Table A. I thought maybe I could do something with relationships but I couldn't figure it out.

Any help is appreciated. Thank you!

Answer 1

Additional Columns

This solution requires the addresses of the ranges containing data, the two column numbers of the ranges to be compared and the number of columns to be added i.e. the number of the last columns from Range1 to be added to Range2.

Before

After

The Code

Sub AdditionalColumns()

  Const cStr1 As String = "A4:D15"        ' First Range
  Const cStr2 As String = "A21:H28"       ' Second Range
  Const cIntCol1 As Integer = 2           ' First Compare Column
  Const cIntCol2 As Integer = 3           ' Second Compare Column
  Const cIntAdd As Integer = 4            ' Additional Columns

  Dim vnt1 As Variant                     ' First Array
  Dim vnt2 As Variant                     ' Second Array
  Dim vntTarget As Variant                ' Target Array

  Dim i As Long                           ' First Array Row Counter
  Dim j As Long                           ' Second Array Row Counter
  Dim k As Long                           ' Target Array Column Counter

  With ThisWorkbook.Worksheets("Sheet1")

    vnt1 = .Range(cStr1)
    vnt2 = .Range(cStr2)

    ReDim vntTarget(1 To UBound(vnt1), 1 To cIntAdd)

    For i = 1 To UBound(vnt1)
      For j = 1 To UBound(vnt2)
        If vnt1(i, cIntCol1) = vnt2(j, cIntCol1) Then
          If vnt1(i, cIntCol2) = vnt2(j, cIntCol2) Then
            For k = 1 To cIntAdd
              vntTarget(i, k) = vnt2(j, k + UBound(vnt1, 2))
            Next
            Exit For
          End If
        End If
      Next
    Next

    .Cells(.Range(cStr1).Row, .Range(cStr1).Columns.Count _
        + .Range(cStr1).Column) _
        .Resize(UBound(vntTarget), UBound(vntTarget, 2)) = vntTarget

  End With

End Sub