vba for matching row data records for specific fields with the main column field that has data to be transfered to another sheet

Question

I have two sheets in Excel. Sheet2 has all the output data table from a data base source. I have been trying to code a VBA that can transfer specific data records from sheet2 to sheet1. I have tried to develop a criteria that can make data from a specific column in sheet2 to be transferred automatically to sheet1 in a specific cell if the row data for specific records of around 4 field columns, matches up with the main column record that has the data to be transferred.

For example I have series of data in sheet 2 as below

            LIMIT    SALES    REGION   LOCATION  ITEM
            422234   4768.24  HR       1         BUIL
            343222   190.73   BP       2         CON
            432220   1494.62  OP       1         EQ
            343332   1302.66  BP       1         AR
            433322   3881.67  BP       1         NO

The criteria is, sales record for item BUIL can only show on a specific cell in sheet1 if the row in sheet2 shows- Region "OP", location "2". Same rule will apply for the limit record to show in sheet1. Sheet1 has limit and sales as columns and items as rows.

I think this might benefit from some sample data to illustrate what you are trying to do. — psubsee2003
Are the rows in your data series on sheet 2 unique? For example, for BUIL there will only ever be one record with region HR and location 1? — Alex P
@Remnant thank you for your response. Yes the data series on sheet 2 are unique. — user1330471

Alex P Alex P · Accepted Answer · 2012-04-17T15:47:00

The following might help you get started.

Suppose you have your database data (as shown) in Sheet 2 starting in Range A1 and on Sheet1 you have as follows:

    A        B        C
1   Item     Limit    Sales
2   BUIL
3   CON
4   EQ
    etc...

The following code will work down your list of items in Sheet1 and if it finds a match in your data on Sheet2 it will then check to see if the criteria are met i..e Region = "OP" and Loaction = 2:

Sub GetSalesAndLimit()
    Dim items As Range, entries As Range, item As Range, entry As Range, row As Long

    Set items = Range("A2:A" & Range("A2").End(xlDown).row)
    Set entries = Worksheets("Sheet2").Range("E2:E" & Worksheets("Sheet2").Range("E2").End(xlDown).row)

    For Each item In items
        For Each entry In entries
            With Worksheets("Sheet2")
                If entry = item Then
                    If entry.Offset(0, -2) = "OP" And entry.Offset(0, -1) = 2 Then 'Criteria is region = 'OP' and Loaction = 2
                        item.Offset(0, 1) = entry.Offset(0, -4) 'Limit
                        item.Offset(0, 2) = entry.Offset(0, -3) 'Sales
                    End If
                End If
            End With
        Next entry
    Next item
End Sub

vba for matching row data records for specific fields with the main column field that has data to be transfered to another sheet

1 Answers