Excel to get value which is near specific words

Help
,
1

Hi,

I have two excel files, DT1.xls is like payment details in which i need to find specific cells which place can vary, but includes same expressions “Price” “date” and get cells value which are nearby one step right. Then by expressing those values into variables check if those variables exists in same line of other huge excel sheet taken from bank with all payments DT2.xls. any ideas of how to do that?

Thanks

1 Like
2

Please search the forum for comparing two excel files. Essentially there are a few methods - the easiest being looping through the first Excel file and saying for each row check if there is a matching row in excel file 2.

RD

3

So that’s the problem :slight_smile: that there is only comparrison and i need something else:

  1. there is no datatable to read column names, and there is no need to compare
  2. it would be more likely to press ctrl+F, input Word which you search and get its possition (but not with keyboard inputs and recording secuence, i want this with excel extention which is in UIpath as it is much faster)
  3. by getting the possition of required Word i can determine required value which is two cells right.

But again as i mentioned, there is no datatable, no headers.

Thanks ,)

4

I don’t understand why you can’t read the Excel sheets into data tables. Could you provide your workflow with more information please?

5

this is the excel file in which i need to take details (it should find the Word “CARDS” and take cell string which is 3 cells right, if that is empty, then it should search for Words “betalning swish” and take the cell string which is 3 cells right.makdonaldas222.xlsx (113.1 KB)

I can read it in datatables but don’t get how it can help if there is no headers?

6

Bit of an ugly format but you can still read into a datatable. My preferred option would be to transpose the crucial information somehow with nice lables in column A and the value in column B - in your template you could have headers.

If you want to do it more quickly then you can reference the data by using row(1).ToString (this pulls the second column) - you could check for example does row(1).ToString contain the text “food” and if so then pull the value from row(3).ToString where column 3 is the value you need for that particular item.

Does that make sense?

7

Well, as i am new to UIPath and have not much knowledge in vb.net so it is quite hard to do it myself without seeing example. This format will be Always like this, Words will stand in their columns Always but they can just change the row. So if you could drop some uipath templates it would be perfect :slight_smile:

8

Have you been through the UiPath Academy? This will help you improve your knowledge.

Also Microsoft Virtual Academy (.net basics) is a really good course.

Better to learn the skills before trying to solve the problems.

9

Trying to keep it simple, yet efficient, you could do something like the below pseudocode example:

For each row In DT1
    Assign DT2RowMatched = DT1.AsEnumerable.Where(Function(row2) Cdbl(row2("Price").ToString.Trim.Replace("$","")) = Cdbl(row("Price").ToString.Trim.Replace("$","")) ).ToArray(0)
    If CDate(DT2RowMatched("Date").ToStringTrim) = CDate(row("Date").ToStringTrim)
       Do Actions

In summary, loop through each row of DT1, find the matched row in DT2, then check if certain column match and do something. I used CDbl() to convert Price to a number and CDate() to compare dates, however the date conversion might require .Parse method instead if it’s not in a recognizable format.
EDIT: DT2RowMatched is of type DataRow.

Those are my 2 cents.

Regards.

2 Likes
10

i am still going through those Courses :slight_smile:

11

Is there a way for you to make it on UiPath for better understanding?

Thanks