Excel Lookup with a word contains dot (.) in the middle

Help Activities
, , ,
1

Hi there,

I have an excel that contains two sheets, one for reference and other one is the main one.
Reference one has two columns those columns is like key-value pair ones.

What i doing here is, if a key value matches the value on the other sheet’s related column’s value, it gets the key-value’s value part and paste it on the main sheets’ corresponding column.

The problem is, im doing this very well, except for one key-value pair which has dot (.) in the middle which is called Rental.Com

Because all others are done without any problem and just this Rental.Com has, i thought the problem is just because of that dot.

For a solution i thought that i can get the substring like just Rental and do the lookup but there may be one than one key-value pair that has Rental in it.

By the way im not using Vlookup activity because that not suits to my project. So, writing a code to done this project.

So is there any other solutions?

2

Hello @ozlem.ciftci, see if this works for you:

Assign
DataTable dtReference = new DataTable()
DataTable dtMain = new DataTable()
List matchingValues = new List()

Excel Application Scope
Input: Path to Excel File

Read Range
Input: Reference Sheet Name, Output DataTable: dtReference

Read Range
Input: Main Sheet Name, Output DataTable: dtMain

For Each Row in dtMain
Assign
string keyToLookup = row(“YourKeyColumn”).ToString() // Replace with the actual key column name

Assign
DataRow matchingRows = dtReference.Select(“YourKeyColumn LIKE '%” + keyToLookup + “%'”) // Replace with the actual key column name

For Each matchingRow in matchingRows
Assign
string matchingValue = matchingRow(“YourValueColumn”).ToString() // Replace with the actual value column name

  Add To Collection
     Collection: matchingValues
     Item: matchingValue

// Handle the matchingValues list as needed (combine values, choose one, etc.)
// You can add logic here to decide how to handle multiple matches

// Finally, write the matching value(s) to the corresponding column in dtMain
row(“YourOutputColumn”) = String.Join(“,”, matchingValues) // Replace with the actual output column name

Clear Collection
Collection: matchingValues
End For Each

Cheers! :slight_smile:

4

Hello @rodrigo.simao,

Thank you for your detailed reply firstly. :slight_smile:

What i did is so close to yours but there may be a thing should be changed on mine.
Please, let me explain what i did too.

Have one DT for Main, (DT_Main)
Have one DT for Reference (DT_Ref)

For finding the matches,

in For Each Row in Data Table activity,

ForEach ‘row’ In DT_Main
Using Assign : rowValue = row(“DT_Main_Col_Name”).ToString

then using IF :
Condition : DT_Ref.AsEnumerable().Any(Function(r) r(“DT_Ref_Column_Name_Key”).ToString().Equals(rowValue) AndAlso (row.IsNull(“FilteredColumn”) AndAlso String.IsNullOrEmpty(row(“FilteredColumn”).ToString())))

(You can ignore FilteredColumn condition there, coz thats another rule that I have to indicate)

then in this If Condition,
I am using two Assign activities;

1st Assign :
matchedValue = DT_Ref.AsEnumerable().FirstOrDefault(Function(r) r(“DT_Ref_Column_Name_Key”).ToString().Equals(rowValue))(“DT_Ref_Column_Name_Value”).ToString()

2nd Assign :
row(“FilteredColumn”) = matchedValue

That perfectly works with others except Rental Com with dot

So according to my code, do you think i should change something in 1st assign activity up there?