Is there any way to get the values of a matrix by specifying the rows and columns?

I’m looking for a way to get the target value in the row and column values of a matrix like the one below, if it is described in Excel.


If ColumnA and RowA are specified: 1
If ColumnB and RowD are specified: 20
If ColumnF and RowF are specified: 36

At first, I tried to use the Filter Data Table activity to narrow down the matrix, but I found that I had to set the row and column values in advance, so I gave up.

Is there any way to get the target values by specifying the rows and columns?

Thank you very much for your help.


Can you try the following expression?

dt.Rows(Array.IndexOf(dt.AsEnumerable.Select(Function(r) r(0).ToString).ToArray,"RowD")).Item("ColumnB")


1 Like

Maybe you are interested on an mediate datastructure for quick access - done with a dictionary

when we readin the excel by activated add headers we would get following datatable (reduced sample data set was used):

With the help of LINQ we can bring it into a Dictionary(Of String, Dictionary(Of String, Int32))


(From d In dtData.AsEnumerable
Let dc = Enumerable.Range(1, d.Table.Columns.Count - 1).ToDictionary(Function (x) d.Table.Columns(x).ColumnName, Function (x) CInt(d(x).toString))
Let t = Tuple.Create(d(0).toString, dc)
Select v=t).ToDictionary(Function (x) x.Item1,Function (x) x.Item2)

And will get:

Accessing the values:

1 Like

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