I process Excel files which contain very unstructured datatables. It’s not really important why they are unstructured but the general idea is that you can’t preliminary know which index a certain cell will have.
So I use the Read Range activity and store it to a DataTable variable. Let’s call it DT
- I need to find whether there’s a row in the table with a certain word, let it be “Amount”. So I need to understand whether there’s a row containing the word “Amount”.
- If the word exists I need to understand which column and line it is.
- If there are multiple cells with the value “Amount” then I need to understand which one occurs earlier in terms of columns.
What is the best way do it?
My idea is pretty straightforward but I feel like it can be done much easier and nicer.
The idea is to use for each row activity, then use another loop to scan every column of the row and if the value is what I need then stop the counters. But the solution will look too bulky and more difficult to maintain in future.