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.
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.
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?
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
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):
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)
