Alternatives for Excel LookUp Activity

Help Activities
, ,
1

Hi Guys!!!

I need to locate a cell in Excel. I’m using a lookup activity, but as we know, it will give us the position of the first cell with a matching value, but I need the positions of all the cells that match the condition.

2

In such case we can use a LINQ and would work against DataTables

Sample: dtData

Region Country
APAC AU
EMEA DE
EMEA FR
APAC IN
APAC JP
AMER US

Assign Activity:
arrIndex | int32 Array =

(From t In dtData.AsEnumerable().Select(Function (x,i) Tuple.Create(x,i))
Where t.Item1("Region").toString.Equals("EMEA")
Select i = t.Item2).DefaultIfEmpty(-1).toArray()

it will return: 1,2 - as it is 0-based
When no row was found, it will retrun -1

3

@BharathKamalapur

yourDataTable.AsEnumerable() _
             .Select(Function(row, index) If(row("ColumnName").ToString() = "YourCondition", $"{Convert.ToChar(65 + index)}{index + 1}", Nothing)) _
             .Where(Function(cell) cell IsNot Nothing) _
             .ToArray()
1 Like
4

Hey @BharathKamalapur

  1. Initialize a List of Integers
listOfMatches = New List(Of Integer)

  1. Use For Each Row to Iterate through DataTable:

  2. Inside the For Each Row:
    → If

row("YourColumnName").ToString = "YourSearchValue"

->Then:

listOfMatches.Add(dtExcelData.Rows.IndexOf(row) + 1)

After the loop, you’ll have a list of all row indices where the cell values match your condition. You can then use this list for further processing.

5

when is meant, that all rows and cells are to check and a typical Excel range (like: AZ2) is needed we can do:

Assign Activity
myOffset | Int32 = X
X =1, when add headers is not ticked, 2 when add headers is ticked, or any other offset

Assign Activity:
arrRanges | String Array =

(From rix In Enumerable.Range(0,dtData.Rows.Count)
From cix In Enumerable.Range(0,dtData.Columns.Count)
Where dtData.Rows(rix)(cix).toString.Trim.Equals("YourSearchKey")
Let cLetter = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(cix + 1)
Let rng = String.Format("{0}{1}", cLetter, (rix + myOffset).toString)
Select v=rng).toArray

EDITED: fixed: cLetter.toString → cLetter, as it is already string

1 Like
6

@BharathKamalapur

Dt.AsEnumerable() _
             .SelectMany(Function(row, rowIndex) row.ItemArray.Select(Function(cell, columnIndex) New With {.Value = If(cell IsNot DBNull.Value AndAlso Not String.IsNullOrEmpty(cell.ToString()), cell, Nothing), .Row = rowIndex + 2, .Column = columnIndex + 1})) _
             .Where(Function(cell) cell.Value IsNot Nothing AndAlso cell.Value.ToString() = "5916") _
             .Select(Function(cell) $"{Convert.ToChar(64 + cell.Column)}{cell.Row}") _
             .ToArray()

Sequence15.zip (2.0 KB)

This gives output as if your value in three cells like “A1”,“B12”,“C12” in this way it gives the value

If you are having any issues with this query please post your problem

1 Like
7

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