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

salu - Nov 4, 2022 at 02:35 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 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!

2 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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


1

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

0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
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