Numeric and Alpha Numeric Min Value (Excel columns)

Hello. I am trying to work with excel data where there are several columns. The column value can be numeric and alpha numeric. I am looking for a minimum value of all columns if there are any numeric value and the result is subtracted by 36. If all columns have value of “never done” than the result should be “never done” Please see example below where its taking the minimum value of all the columns and subtracting the result by 36 to give the final result.

Please let me know if you have any further questions. Thanks :slight_smile:

Hi,

Can you try the following sample?

arrNumericValue = CurrentRow.ItemArray.Where(Function(o) Int32.TryParse(o.ToString,New Int32)).Select(Function(o) CInt(o) ).OrderBy(Function(i) i).ToArray

Sample20230227-1L.zip (9.6 KB)

Regards,

Good Morning Yoichi. That worked but I have another addition to the problem. What if there was another column added to the mix. Lets say Col1-Col7 are in a row and then there another column which was added few columns after. How would the formula change. See example below. I want to see the results of Col1-7 and also Col8 which is not next to Col7. Please advise. Thanks :slight_smile:

Hi,

In this case, create array of column name in advance (let’s say arrCol), then use the following expression.

arrNumericValue = CurrentRow.ItemArray.Where(Function(o,i) arrCol.Any(Function(c) c=dt.Columns(i).ColumnName) AndAlso Int32.TryParse(o.ToString,New Int32)).Select(Function(o) CInt(o) ).OrderBy(Function(i) i).ToArray

Sample20230227-1Lv2.zip (16.0 KB)

Regards,

1 Like

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