Compare 2 columns to find the 3rd value and return the 4th
ClosedTrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2022 at 11:32 AM
- Compare 2 columns to find the 3rd value and return the 4th
- Tentacle locker 2 - Download - Adult games
- Fnia 2 - Download - Adult games
- The sims 2 download - Download - Simulation
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Plants vs zombies 2 pc download - Download - Strategy
2 responses
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
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
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
Nov 8, 2022 at 12:21 AM
Hi TrowaD,
This was super helpful. Thanks a million!