How to extract and update account number if account code from code column foun

Help Activities
, , , , , ,
1

I have multiple columns in which i want to check account code column contains BG15 IF exist then update status column with account number from AccNo column else no action required in status column without looping as there are more than 50000 data.

For e.g.
If AccCode column contains BG15 code before 1st comma then it should return account number before 1st comma from accnum column and update to status column
For example input data:

Screenshot_20220321-025044__01
Screenshot_20220321-025044__01988×363 72.4 KB

Output should be:
Screenshot_20220321-024710__01
Screenshot_20220321-024710__011002×409 107 KB

2

Hi @Mansi_Mhatre

This is interesting!!!

you need to get data as a data table lets say dt_acc_codes
loop through each row
below pseudo code is for one iteration
start For Each
split the values in each cell at “,”
lets say
Split_Code = {“ETG88”,“TBG15”} Split_Code is String[ ]
Split_Acc = {“AJ125648”,“AC788555”} Split_Acc is String[ ]
if Split_Code current_element contains “BG15”
get index_code = Array.IndexOf(Split_Code, current_element)
Status_Acc = Split_Acc(index_code)
update Status with Status_Acc
End For Each

you need to handle scenarios if Split_Code and Split_Acc are having only one element

hope this is helpful

3

Hi @Mansi_Mhatre ,

Could you give this a try?

(From row In dt_sampleData.AsEnumerable()
Let lstItem = row("AccNum").ToString.Split({","c}, StringSplitOptions.RemoveEmptyEntries).Last()
Let status = If(row("AccCode").ToString.Trim.Split({","c},StringSplitOptions.RemoveEmptyEntries).Any(Function(a) a.Contains("BG15")),lstItem,"")
Let ra = New Object(){row("AccCode"),row("AccNum"),status}
Select dt_result.Rows.Add(ra)).CopyToDataTable()

BG15.xaml (8.0 KB)

Kind Regards,
Ashwin A.K

4

I have more than 50 columns before this columns how to get that columns also with this query.
I want those column as well in output dt along with updated status column.

5

Hi @Mansi_Mhatre ,

Its impossible for us to provide you with a solution unless you provide little more detail, like which indexes the columns you have shared with us are present at, or even if you had a sample data sheet that you could share with us, that would be really helpful.

Kind Regards,
Ashwin A.K

6

Before given 3 columns i have 50 columns with data with column status blank…so just want to fill status column if any required condition match. I want that 50 columns along with updated Status as well.

7

Hi @Mansi_Mhatre ,

So you mean to say that there are 50 columns, after which the three columns you have shown us are present?
If so, then update the second last line like so
this → 

Let ra = row.ItemArray.Take(50).Concat{row("AccCode"),row("AccNum"),status}

If there are 50 columns, with the 50th column being the status column, then try this → 

Let ra = row.ItemArray.Take(49).Append(status).ToArray()

Kind Regards,
Ashwin A.K

8

What if AcctNo and AccCode column is blank…?

If both columns are blank.

9

have a look below on strategies for updating a column value:
How to Update Data Column Values of a Data Table | Community Blog

in your case we would recommend for a filtering and updating the relevant rows / columns within a for each:

  • 2.1 For Each Activity Approach with LINQ filtering
  • 2.2 Other Alternative Approaches (DataView)

for calculating the AccCode we would recommend to calculate the index of BG15 on AccCode and taking the last found index for taking the ACCNum

grafik
grafik827×132 26.1 KB

Kindly note: manipulated first value for demo purpose

10

It is only providing last value of accnum. If input is
AccCode- TBG15,UKT19,EKO78
AccNum-12345,7899,56788
Actual output-
Status-12345

output status should be- matching index of AccCode not last index. Given code only providing last value i.e. 56788 not
Status-12345