How to get the columnname of a specific value in a row without getting knowing the column name or row

Help IT Automation
, , ,
1

Hi,

I have a complicated task for me. We have this excel file that is being downloaded from a website, however, the columns in that excel file are inconsistent.

For example, sometimes excel file has columns A, B, C, D, sometimes it has A,B,C,D,E, F sometimes it has A,B,C,D,E,F,G,H, and sometimes A,B,C,D,F. The columns are removed if there are no data in it, and a new column is added if there is data for it.

Now, my initial solution is to get the row value because it is always there for example row value is “HKD Coins”, I need to find where column this value is located without knowing the column name as columns are changing.

I cannot use the following code below because I need to specify column name and row index.

yourDatatable.item(“ColumnName”).ToString.
yourdataTable.rows(0).item(“ColumnName”).ToString

Any ideas on how to get through this?

2

You can Use LookUp range
It will give you cell reference in which That String or text present
Ex: Input Text: “HKD Coins”
Range:
OutPut:LookUp_Out ------------>B5

1 Like
3

is there any way without using excel lookup range? I already have it in datatable so i think it will complicates things

4

So u can do like this
Use For Each Row assign Index in output property say IndRow
Then
In that use if row.ItemArray.Contains(“HKD Coins”)
Then
Array.IndexOf(row.ItemArray,“HKD Coins”) it will give u column Index
and IndRow will give Row Index

1 Like
5

I’ll try this.

6

doesn’t solve my problem. Any other way to get the column index without specifying the row index?

7

What problem u are facing with this

8

@dhennespiritu

Find an introduction into an alternate approach:

we can do it with 2 steps (first Build datatable is simualting readin the data)
grafik

given some data:
grafik
And the configuration of searchtem as variable:

grafik
grafik875Ă—146 8.16 KB

With the help of some techniques we can quickly setup
Enumerable.Range is creating a sequence starting by the start value with a given length:
grafik

A cartesian product brings the elements into tuples from each
Set 1: A,B,C,D
Set 2: 1,2
Result A1, A2, B1,B2 …D1, D2

Following Linq exploits these and some more concepts:

(From rix In Enumerable.Range(0,dtData.Rows.Count).ToList
From cix In Enumerable.Range(0, dtData.Columns.Count).Where(Function (x) dtData.Rows(rix)(x).toString.Trim.Equals(SearchTerm)).ToList
Let ra= New Object(){rix, cix, dtData.Rows(rix).Table.Columns(cix).Columnname}
Select dtPositions.Rows.Add(ra)).ToList
  • Create a sequence of all RowIndex
  • Create a sequence of all ColIndex, but filter it only columns where the Value is matching the SearchTerm
  • create an Object Array (ra) of (RowIndex, ColIndex, Columnname
    • The columnname we do find with the help Table Navigation property anchored by RowIndex and selecting with ColIndex
  • use ra to add a new row to the datatable dtPositions

et voila, we get the result:
grafik

As it is dynamic, changing columns will not block.

Find starter help here:
GetAllRowColIdx_By1Value.xaml (9.5 KB)

1 Like
9

Hi @dhennespiritu,

You have an acitivity called "find " here. It will return the row index and column Info.

Thank you
Balamurugan.S