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
- Euro truck simulator 2 download free full version pc - Download - Simulation
- The sims 2 download - Download - Simulation
- The grim reaper 2 apk - Download - Adult games
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!