after vlookup there are some blanks and N/A in some cells
the blanks and N/A should be replace by the comments
how to put the condition
Read Range - Input: “YourFilePath”, Output: dtData
For Each Row - Input: dtData
If - Condition: row(“YourColumnName”).ToString = “” Or row(“YourColumnName”).ToString = “N/A”
Assign - To: row(“YourColumnName”), Value: “YourComment”
End If
End For Each Row
Write Range - Input: “YourFilePath”, DataTable: dtData
Book1.xlsx (9.7 MB)
i have three conditions like 1.if n/a for both the columns then the comment should be like date not available
2.if blanks for both the columns then data is missing
3.if the date is 1/1/24-31/1/24 then the date is in current period
4.if date have blanks and acknowledgment have date then date is availabe and acknowlgemnet is not available
can you send sample code
For Each row As DataRow In YourDataTable.Rows
If String.IsNullOrWhiteSpace(row("YourColumnName").ToString()) OrElse row("YourColumnName").ToString().ToUpper() = "N/A" Then
row("YourColumnName") = "YourComment"
End If
Next
Hello @anjani_priya
Use IfNA condition with Vlookup Formula on Excel
=IFNA(VLOOKUP(A13879,'https://*****/Documents/Desktop/sample excel files/[SampleCSVFile_2kb .xlsx]Sheet1'!$A$1:$C$522313,2,0),"Your value")
is this expression works like if both the columns have N/A and both the columns have blanks
can you share the solution
For Each r As DataRow In in_dt.AsEnumerable()
If Not r("Date").ToString.Equals("") And r("Acknowledgement").ToString.Equals("") Then
r("YourColumn_Name")="Missing"
End If
If Not r("Date").ToString.Equals("N/A") And r("Acknowledgement").ToString.Equals("N/A") Then
r("YourColumn_Name")="False"
End If
Next
Note :- 
r(“YourColumn_Name”) you can replace “YourColumn_Name” where you need to type
I have another condition that if date and acknowledgement has between 1/12/2023-31/12/2023 these dates then the comment should be ‘date and ackn is winthin month’ in next column corresponding to the column containing dates. can you tell the condition
can you tell the conditions for date
Dim startDate As New DateTime(2023, 12, 1)
Dim endDate As New DateTime(2023, 12, 31)
For Each r As DataRow In in_dt.AsEnumerable()
If Not String.IsNullOrEmpty(r("Date").ToString) And Not String.IsNullOrEmpty(r("Acknowledgement").ToString) Then
Try
If Convert.ToDateTime(r("Date")).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") OrElse Convert.ToDateTime(r("Date")).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") And Convert.ToDateTime(r("Acknowledgement")).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") OrElse Convert.ToDateTime(r("Acknowledgement")).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") Then
r("YourColumn_Name")="date and acknowledgment is winthin month"
Else
r("YourColumn_Name") = "Not date and acknowledgment is winthin month"
End If
Catch ex As SystemException
If DateTime.ParseExact(r("Date").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") OrElse DateTime.ParseExact(r("Date").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") And DateTime.ParseExact(r("Acknowledgement").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") orelse DateTime.ParseExact(r("Acknowledgement").ToString, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") Then
r("YourColumn_Name")="date and acknowledgment is winthin month"
Else
r("YourColumn_Name") = "Not date and acknowledgment is winthin month"
End If
End Try
End If
Next
you can try this for dates
@anjani_priya
Can you try this code i have done some change in the code
Dim startDate As New DateTime(2023, 12, 1)
Dim endDate As New DateTime(2023, 12, 31)
For Each r As DataRow In in_dt.AsEnumerable()
If Not String.IsNullOrEmpty(r("Date").ToString) And Not String.IsNullOrEmpty(r("Acknowledgement").ToString) Then
Try
If Convert.ToDateTime(r("Date")).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") AndAlso Convert.ToDateTime(r("Date")).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") And Convert.ToDateTime(r("Acknowledgement")).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") AndAlso Convert.ToDateTime(r("Acknowledgement")).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") Then
r("c3")="date and acknowledgment is winthin month"
Else
r("c3") = "Not date and acknowledgment is winthin month"
End If
Catch ex As SystemException
If DateTime.ParseExact(r("Date").ToString, "d/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") AndAlso DateTime.ParseExact(r("Date").ToString, "d/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") And DateTime.ParseExact(r("Acknowledgement").ToString, "d/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") >= convert.ToDateTime(startDate).ToString("yyyy/MM/dd") AndAlso DateTime.ParseExact(r("Acknowledgement").ToString, "d/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy/MM/dd") <= convert.ToDateTime(endDate).ToString("yyyy/MM/dd") Then
r("c3")="date and acknowledgment is winthin month"
Else
r("c3") = "Not date and acknowledgment is winthin month"
End If
End Try
Else
r("c3") = "Date or Acknowledgement is blank"
End If
Next
input :
output:
@anjani_priya
can you check that variable type of dec ?
The variable type should be System.Data.DataTable
ashokkarale
Yoichi
postwick
Anil_G
kishan.savaliya
Laura_Hubbard
Tselisehang_Molebatsi
Byrnecut
Lahiru.Fernando
wahyumrizqi