How to get the value from the excel cells

Learning Hub
, ,
1

Hi,
I have two excel files, ones is to get the values and input to the others. May I know how to FY (col L) if there is an entry on any cells ( col M3 to P7) and 1st entry in col Q.
I need the value FY21 and 50 to input to the others excel file. Please assist. Thank you.

Capture
Capture835×515 33.6 KB

2

Hi @Perri,

As I understand correctly, you wish to transfer data from one sheet to another based on a few criteria. There are several way to do this.

  • You could build a VBA Macro which does the task for you,

  • or you read the source excel file into a datatable and then uses several methods to search this datatable for the criteria.

  • Or if the values are always at the same location in the source excel sheet you could use the read cell activity.

If you are going for the Read Range activity and use a datatable to search in, take a look at LINQ or use the following syntax to locate data in the datatable:

dt.Rows(0).Item("ColumnHeader").ToString
or
dt.Rows(0).Item(0).ToString
itterate through the columns and rows to find your data.
Or use a for each loop.

I hope this helps :wink:

3

Thank for your advice :slight_smile:

i have tried to read range and get the following datatable . From there , may I know how to iterate through the data and get FY?? if there is value in the same row.
From this example , I should get FY21,
image
image

Thank you!

4

Hi @Perri,

I don’t know how much you know about DataTables, but you will notice that each row has the same amount of comma’s as the header row. Therefore, column0 is the same as “M” and column5 is the same as “Q”. As you use the for each loop you look at each row and see if anything value was added.
I would do it the following way:

  1. have a incremented value which keeps track on how many values were found within the table;
  2. increment this value by 1 each time a new value is found;
  3. at the end check if at leas one item was found and if the 1st entry in col Q was filled
  4. get FY21 value

image
image396×723 13.4 KB

in the upper image you can see how I would have done it. The value in the the assign is:
If(row("ColumnName").ToString <> "",counter + 1, counter + 0)

‘counter’ is here the value which is incremented. Make sure that this variable is set to integer and its scope to the highst level.
image

This is how I would have done it.Main.xaml (10.6 KB)

6

Thank you for your help.
But is it possible to show “FY23” if the data table set this way
image
Another example would be if there are multiple values , the output would only be “FY22” as it only capture the first number
image

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.