How to get line number in Excel with keyword?

Help
,
1

Hi all,

I need help to get Excel line number to add offset-cell value. Does anybody know the fastest performance way to get line number with keyword?

Condition:

  • Target Excel has keyword in column A. (duplicate not allowed)
  • I would like to set value to column B depending on column A value.
  • I know one way describing below but it possibly low performance.
  1. get column A data
  2. for each row
  3. if keyword exist in the row, get line ID with counting up.
  4. end for
  5. update column B with line number that has obtained in step 3.
    …

I assume this is VB knowledge matter. If anybody know something plz let me know.

Regards,
J,

1 Like
2

@Jumbo
Use this
int a=List.IndexOf((From p In dt.Select() Select Convert.ToString(p.Item(“Column A”))).ToList(),“Key_Word”)

int a is the index of keyword

Regards
Mahesh

1 Like
3

Here is my approach which only takes 2 steps:

  1. Filter table to list of rows that contain the keyword.
  2. Loop through those rows and update column B of those rows
Assign rowlist = datatablevariable.AsEnumerable.Where(Function(r) r(0).ToString.Trim.ToUpper.Contains("keyword")).ToArray
For each row In rowlist  =>TypeArgument as DataRow
    Assign row(1) = row(0).ToString.Trim

The above will update “All” rows that meet the keyword. If you want to only update the first match then an alternate version would not need the loop:

Assign rowlist = datatablevariable.AsEnumerable.Where(Function(r) r(0).ToString.Trim.ToUpper.Contains("keyword")).ToArray
Assign rowlist(0).Item(1) = rowlist(0).Item(0).ToString.Trim

The above just simply pulls the first row that matches then updates column B.

(also, if you have multiple keywords, then you need to add that in the .Where() filter assignment with an AND or an OR with the condition.)

So, you really don’t need the row index of the match cause when you filter it to an array it updates in the original datatable.

If you do want the row index, you can just take the row and place it inside an IndexOf:

datatablevaraible.Rows.IndexOf( row )
or
datatablevariable.Rows.IndexOf( rowlist(0) )

Regards.

1 Like
4

Thanks @MAHESH1 for your reply!! It’s one of good idea creating a list and use index.
That’s might be work.

Best regards,
Jumbo

5

Hi @ClaytonM,

Thanks for your reply and I appreciate your idea and I feel it works most of time, however, that’s does not work for my problem, because, I didn’t told this, my objective Excel is very hard restricted and cannot change except only few cells… Thus cannot filter it…

Anyway, your solution is might be faster if there is many target data to update so I will note it!!

Best regards,
Jumbo