How to match values in excel with values in a dictionary key

Hi everyone. I have an excel sheet with Credit and Debit columns which contain values, they cannot contain values at the same time. I have a dictionary, they Keys contain amount values, the Value contain file path values.

What I am trying to achieve:
Lets say i have a value such as: 1923 from my excel sheet, i want to match it in they dictionary key even if the value is : 000192300.

Once the number sequence matches:

  • I want to write to the sheet creating a column (Confirmation status) - Yes, Then create another
    column( File Path) - which would add the file path from the dictionary Value.

Want to make sure if the number is from cell A2,write all the information in the same row.

How can i achieve this

Hello @Mpumi_Setshedi

You can pad the number read from Excel as per this documentation below(that shows how to add leading zeros. A bit more research maybe required to see how to add trailing zeros) before you make an exact comparison.

The Dictionary Object has a ContainsKey method that you can use to find the matching value once you managed to format the number from the Excel Cell with the right amount of zeroes.

You may want to add the columns before the comparison process begins. If you have it in your comparison logic then you have to make sure you skip the add column actions after they have been added the first time.

I hope this helps.

Hello @AndyMenon

Thank you for sharing this resource after much battling today. The only thing is the values I am trying to reference are different data will not always have zero’s.

For instance one of the values in the excel are: 225776, im attempting to match the number sequence which would (225776)5775 in my Dictionary Key, Is there another way I could tackle this?

Hi @Mpumi_Setshedi ,

You may have to do something a bit more involved. If we assume that number from Excel is always contained in the Dictionary then

One simple approach is as follows:

Use Dictionary.Keys to get a list of all Keys into a collection
`Example: keyColl = YourDictionary.Keys`

'Loop through Excel Rows
For Each row in DataTable_Excel
'Get value of Excel Cell 
cellValue = row("column_name").ToString()
'Check if the Excel value is contained in the Dictionary Key collection using the Contains function
'There are ways to do it using Lambda functions, but for now you may have to loop through the key collection 

For each k in keyColl
 if k.Contains(cellValue)= True then 
   get the filePath value of that key from your dictionary 
   filePath =  YourDictionary(k)
   'Write filePath to your Excel Cell in current row
   'Write Confirmation to your Excel Cell in current row
end if
End For
End For

I hope this helps

Hello @AndyMenon ,

Thank you for your reply once again - I have been building the workflow:

I attempted to put this into an assign activity, changed the variable type to Dict of(string, string): dict.Keys

I have an IF Statement which checks if there are values in Credit or Debit Column then replaces all dashes,commas,fullstops not sure if i did something wrong: isNothing(row(“Credit”)) OrElse String.IsNullOrEmpty(row(“Debit”).ToString.Replace(“-”,“”)Replace(“,”,“”).Replace(“.”,“”).Trim)

If the Then handles Cell value for Debit column, would it be possible for Else handle Credit

Type argument is Dict?

Thank you for providing me a pathway for my solution with the first message

You are missing a dot before the second Replace . Instead of using “” you can use String.Empty in the replace functions.

You might want to check the data type of the KeyCollection variable.

It is Dictionary<T,T>.KeyCollection

For the if conditions ,
I usually use the expression

Object Is Nothing

It’s straight forward in my opinion.


Not sure why the condition has’n’t affected the Data

If you don’t mind, Is my sequencing and interpreation of the code snippet you shared earlier correct? (11.7 KB)