Read values from excel

Help Studio
, ,
1

Hi team,

Attached is the I/p excel file, we need to read the amount value(which is colored) and read its corresponding counterparty name, payment date (which is there 1 row above), for each counterparty go to another sheet get its corresponding code, based on the cell if filled against column R or A/FED perform corresponding steps in corresponding apps and fill as filled in location column for that row.

The data is huge, we thought of fetching colored value and looping back to get the party name and take appropriate steps. do we have other optimal ways of achieving the same.

Eg: read 119621.54 which is on f30 and get its corresponding party name i.e BPL

2

code

input
input1476×807 25.6 KB

3

Hi Manisha,

Since your dealing with huge data, to optimize it, check these out

  1. you could try using VBA code for read entire excel and filtering the colored cell. and write them in temp excel (or another sheet)

  2. Then you can read this temp excel ( or the sheet) using Read range activity. Lets say its “Dt1”

  3. Read the sheet which contain codes in it. lets say it’s variable is “Dt2”

  4. Now instead of Looping them you can use Join Data Tables and select the join type as inner join. Please see the below screenshot for just reference.
    Capture

  5. once done, you will get another new set of data in the respective datatable variable.

hope this helps :slight_smile: :blush:

4

Thank you for your reply pavithra.

here if we filter on colored cell, we would miss out on counterparty name and other values which are present in the previous row.

5

Hi Manisha,

Ohh yes!. thanks for pointing it out. i missed these points.

So, lets forget about the filtering as this wont help. How about if we only keep the “join data tables” and keep the join type as Full

Because doing this, whats gonna happen is that, the new DT which gets created, it will have the consolidated columns from Main sheet which contains the colored cell etc and other sheet which contains Codes.

Hope this helps :slight_smile: :blush: