Excel Table, Lookup value

Hi,

I have Excel table where are two columns:

  • VendorID
  • VendorName

RPA reads with OCR from PDF document vendor ID number.

I need to get vendor name from second column(VendorName) in a row where VendorID matches to vendor ID what was in PDF document.

I think i need to do following steps:

  1. Add Excel table to table variable with GetRange
  2. Do for each loop with table variable for rows
  3. Add second nested for each loop for looping row columns, filtered to VendorID column

Question is: How i get vendor name to variable if second for each loop is filtered to VendorID column?

Or is it possible to add reference to table column somehow, so the table don’t need to filtered?

-mikko

@mikkoVKXBiWReM

Hi Mikko,

In this case you can do as follows:

  • copy values from Vendor ID column to a list variable using Get column action
  • use For Each loop to iterate through the list
  • use the IF action to check whether the value if the VendorID column is the same as OCR value
  • use Excel action Get cell value to copy the value from the VendorName column to a variable.

Alesia

Thanks for fast reply :slight_smile:

That solved the problem in this case.

But what if you need to get values from more than one column? For example:

Table columns

  1. VendorID
  2. VendorName
  3. VendorEmail
  4. VendorClass
  • VendorName you get with GetCellValue(Cell to theRight)
  • VendorClass you get with GetCellValue(End of Row)

But how do you get VendorEmail columns value?

-mikko

@mikkoVKXBiWReM
Yes, you can either use Cell to the right again for VendorEmail and End of row for VendorClass, or, if there will be a lot of columns, it will indeed be better to use the table variable.
In this case, you need to iterate through the first item in each row

And use index in the list in order to populate VendorName and other variables as shown below.

2 Likes

Thank you :slight_smile: Solutions is much clearer what i thought.

2 Likes

I have one table list which contains excel cell value. i am performing foreach loop with table sorted by row.i need to take last value of item. for example if row contains below value

1,amol,mail,true

i want to take “true” value in another list.

Please help me in this case

@amol_sonavane
Hi Amol, please share your recording or a screenshot of the recording here.

Hi please find attached file.

in table, if M2 cell is true then i want E2 sell value. like that if out of five rows contain true values.i want corresponding E* cell value.BirthdayExcel.zip (567.9 KB)

BirthdayExcel.zip (567.9 KB)

Hi ashapkina,

did you find any solution for my question?

Amol,

The solution is almost identical to the one in the post above.

You need to:

  • copy the excel file contents to a table variable using Get Range
  • iterate trough each row in the table and each 13th item in the row (column M)
  • use IF-else argument (item equals true)
  • if the value is true set the value of the 4th item (column E) to a constant variable
  • use Set cell value action to copy the value of this variable to the cell you need (column N in the example attached).

Here is a sample recording (version 1.1.9) and screenshots of the actions.
excel-iteration.zip (2.2 KB)

Alesia

Is there any limit on the table size for table variable?

Anna, there is no limit on the table variable size, but there is currently a limit on the number of characters that can be copied to Excel using Set Range action (65535 characters).

What about the limit on the number of characters in Get Column action?
Thanks