How to find a duplicate number in excel

Hi Friends,

I have an excel with a column named Amount. I need to browse to that excel and find the amount value which i received from a different source in this excel and mark that row as yellow color.Currently it is working. However for a scenario belwo it doesnot.

The received Amount is -123.45
The Amount present in the excel in row 5 is 123.45
The Amount Present in the excel in row 10 is -123.45

therefore according to my current code, the BOT verfies the record and says there is a duplicate data and sends a business exception, but actually it had to mark the row 10 with the color.

The code details is:
The Excel file is read to find if there is data or empty file

if emty–>throw exception
if not—>i have used look up range to get the exact cell details of that value
to verify if tehre is no duplicate number , i am using look up range again with the cell address obtained from above to check if there is any other cell address which has the value
if i receive two different cell address, there is duplicate value
if i recieve only one cell address, there is only one value.

in the above scenario i get two different cell value.hence BE is thrown.

Kindly help me

@RenukaRPA
May I ask you, the Excel you have readin in with a read range, right?

yes , inside excel scope, i am using read range and to find the cell value its look up range inside Excel application scope

I found the solution :

int_countvar=Datanetting.Rows.Cast(Of DataRow)().Where(Function(a) a(“base currency Amount”).ToString() = cstr(In_level4Amount).Trim).Count() - this will give the count of the amount
[Reference :
Dim ndx As Int32
Dim rows = dtSample.Select(“Id = 42”)
If rows.Count > 0 Then
ndx = dtSample.Rows.IndexOf(rows(0))
End If
Return ndx
and

Dim ndx_intarr = dtSample.AsEnumerable().
Where(Function(q) q.Field(Of Int32)(“Id”) = 42).
Select(Function(z) dtSample.Rows.IndexOf(z)).
ToArray()
]
if the count of amount is 0, then there is no value available

rowinde_intarr =Datanetting.AsEnumerable().Where(Function(a) a(“base currency Amount”).ToString() = “7965.68”).Select(Function(z) Datanetting.Rows.IndexOf(z)).ToArray()
nettinqueryDT.AsEnumerable().Where(Function(a) a(“base currency Amount”).ToString() = cstr(In_level4Amount).Trim).Select(Function(z) Datanetting.Rows.IndexOf(z)).ToArray()

the above code will give u the index of the value, as per my excel file, i had to add 3 to the index to fetch the correct index value in Excel

I need to highlight only one value , so I verified the array length, if the lenght is 1, then the

rowindexvalue=rowinde_intarr(0)+1
highlight the range from “A”+rowindexvalue.tostring+":A"+rowindexvalue.tostring

if the array length is greater than 1, then there are duplicate values

Hope this helps…

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.