Compare 2 columns to find the 3rd value and return the 4th

Closed
salu - Nov 4, 2022 at 02:35 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2022 at 11:32 AM

Hello,


Here in my example, if value in Column B (table 2) matches with the value in column A (table 1), then I need to check the first row corresponding to that value which has a string/number or is in green color, and finally return the date corresponding to that value.

Please help!

Related:

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 7, 2022 at 11:50 AM

Hi Salu,

Paste the code below in a standard Module:
 

Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString Then
        mCol = cell.Column
    End If
Next cell
LastAttendanceDate = Cells(mDates.Row, mCol)
End Function

In your second table under the header dates, you will put the formula:
=LastAttendanceDate(Range with dates;Range corresponding to the number)

So for B11:
=LastAttendanceDate($B$1:$F$1,B2:F2)
Drag the formula down for the other dates.

Best regards,
Trowa


Hi TrowaD, 

This was super helpful. Thanks a million!

0

Hey TrowaD,

a little modification here. so in first table for Cell name "1", we see that green box containing data is extended to 2 days. In this scenario also, i need to return the first date in the date column (of table 2). So the answers needs to be as below. It should ignore other cells containing any string value and should only return the first strings date

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Nov 8, 2022 at 11:35 AM

Hi Salu,

Ok, no problem. Some adjustments has been made to the code:
 

Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString Then
        mCol = cell.Column
        LastAttendanceDate = Cells(mDates.Row, mCol)
        Exit Function
    End If
Next cell
LastAttendanceDate = vbNullString
End Function

Best regards,
Trowa

1