How to select specific cell/data from Excel based on Row and Column

Build Studio
#1

Basically I have a table similar to the below:

Dept…Jul…Aug…Sep
AA1…201…309…460
BB2…202…360…520
CC3…302…402…650

I need to be able to select the correct 3 digit number from the excel/DT based on a variable that holds the Dept and Another Variable that holds today’s date, so as each month goes by it then auto-selects the next columns figures.

At present I have read the excel into a DT, filtered it using DTJobNo.AsEnumerable.Where(Function(x)x(“Dept“).tostring.equals(DeptVariable)).toArray

I am able to then select each of the “columns” through the i.e. Array.items(1) assign activity. But was hoping for something a little slicker than using lots of IFs to assign the variable based on the month.

I’ve got it to work, but its a bit too clunky for my liking and would require some updating of the process to ensure it works long term, so would welcome any and all suggestions to make this more robust long term.

Thanks in advance
Dawn

#2

@DawnW
can you clear your question with some more sharp description:

given:

  • datatable as above
  • DeptVariable of Datatype String: e.g Value “AA1”
  • DateVariable of Datatype ? e.g. value ?

Expected result ?

thanks

#3

Its a basic PO creation automation… A QR code is scanned which creates an excel giving the Department, Qty, material, price to order etc.

The Robot reads these, creates variables (currently string), then opens our ERP system to create the PO… My issue is that depending on the required date of the goods (Today+2), and which department has ordered them the Robot needs to select a “Job number” (3 digit number from table) depending on these 2 variables…

i.e. If the request came from Dept AA1 and the required date of the goods was 6th Sept, the Robot would need to charge the PO to Job 460 (table in original question)…

At the moment i’m just using DT.AsEnumerable to select the row from the Datatable and create an Array, then a load of IF statements to select parts of the array.items based on if the required date falls into specific months etc.

However long term this will end up being a lot of IFs as the months and years pass so was hoping someone could come up with a better strategy…

Thanks
Dawn

#4

@DawnW
lets assume a variable called MothCol that is initialized with NowOrRequiredDateOfGoods.AddDays(2).toString(“MMM”)will hold the value Aug

In the datatable the Month Colnames are in the form of Jun,Jul, Aug…

The 460 or for Aug the 309 could be retrieved by

Linq Query syntax:
From d in DTJobNo.AsEnumerable
Where d(“Dept“).tostring.equals(DeptVariable)
Select d(MothCol).toString

can you give a feedback if this would go into the targeted direction? Setting up the handling of more return (toArray, ToList) or checking the must unique result (.Single()) would be possible as well