How to pull data from column based on multiple criteria?

Raj_2823 Posts 7 Registration date Monday August 29, 2022 Status Member Last seen January 13, 2023 - Updated on Jan 19, 2023 at 11:10 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jan 16, 2023 at 12:12 PM

Hi Expert,

I'd like to combine a column value in Mainsheet, where the column values are pulled from Sheets 1 and 2 of the same Excel file using VBA code or an Excel formula.

The only difference between sheets 1 and 2 is the Check Number. To enter the check number into the main sheet. The following criteria must be met in the excel file.

In the snapshot below, I've attached the expected outcome.

Thanks in advance!

Regards,

Raj

3 replies

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 12, 2023 at 11:44 AM

Hi Raj,

In your sample, row 8 doesn't match between Sheet 1 and Sheet 2 (Method column; BT vs Check), but the check number is still placed in the Main sheet. Is that a typo?, or do you just want to pull the matching data from Sheet 2 and ignore Sheet 1?

I always like to asume that sample data is a reflection of the real data (as it should), but I realise that isn't always the case. If it is, then you can use a simple "=" formula to link the cells in order.

Best regards,
Trowa


1
Raj_2823 Posts 7 Registration date Monday August 29, 2022 Status Member Last seen January 13, 2023
Updated on Jan 13, 2023 at 04:24 AM

Hi Trowa,

Thank you very much for your quick response.

I would like to bring the check number in sheet1 from sheet 2 to the relevant row. When the "method", "Payee ID" and "ID" match with sheet2.

Thanks in advance!

Regards,

Raj

0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 16, 2023 at 12:12 PM

Hi Raj,

Sorry, but I'm still a little confused.

Is the Main sheet from your first post actually an updated version of Sheet1? So you only have 2 sheets?

Assuming the above and you insert a column so that column F of sheet 1 is empty the code below compare the 3 values and places the Check Number from sheet 2 in Sheet 1:
 

Sub RunMe()
Dim mFind As Range

Sheets("Sheet2").Select

For Each cell In Range("A2:A6")
    Set mFind = Sheets("Sheet1").Columns("B").Find(cell.Value)
    If Not mFind Is Nothing Then
        firstaddress = mFind.Address
        Do
            If cell.Value & cell.Offset(0, 1).Value & cell.Offset(0, 2).Value = _
            mFind.Value & mFind.Offset(0, 1) & mFind.Offset(0, 2) Then
                mFind.Offset(0, 4).Value = cell.Offset(0, 3).Value
            End If
            Set mFind = Sheets("Sheet1").Columns("B").FindNext(mFind)
        Loop While mFind.Address <> firstaddress
    End If
Next cell
End Sub

Best regards,
Trowa


0