How to separate 1 sheet to several columns based on condition

test file.xlsx (11.0 KB)

Hello, all

I’d like to separate ‘Sheet1’ to several columns based on Account value.

  1. There is only 1 sheet which is ‘Sheet1’ at first

  2. Create new sheet for each account.

  3. If possible, can I add column name for each sheet…?
    Account/Description/In/Out

I’d like to build above process but hard to think proper design.
Thank you for giving advise always.

@Dorothy_lee

  1. First use Read Range activity to read the data from excel and it will give output as DataTable. Let’s say ‘dtAccountsData’.

  2. And then find unique account by using below expression. dtUniqueAccounts is of type DataTable.

         dtUniqueAccounts = dtAccountsData.DefaultView.ToTable(true,"Account")
    
  3. And then use ForEach Row activity to iterate one by one account as below.

        ForEach Row in dtUniqueAccounts
                dtAccount = dtAccountsData.Select("[Account] = '"+row("Account").ToString.Trim+"').CopyToDataTable
                  Use **Write Range**activity to write account wise data into separate sheet and pass below values to **Write Range** activity.
    

DataTable: dtAccount
Sheet Name: row(“Account”).ToString.Trim.Split("-"c)(1)

2 Likes

@Dorothy_lee

Check below output

Follow as below

Read Range activity to read excel → Output->dtTest
Create another Data variable → DtTest1
Assign DtTest1->dtTest.Clone
Use Assign activity to group the columns as below

(From p In dtTest.Select()
Group p By ID=p.Item(“Account”).ToString Into Group
Select Group(0)).ToArray.CopyToDataTable()

Once done place For Each row activity → dtTest1

Inside For Each row Place Filter Datatable

Configure as above, dtTestTemp is a new Datatable

Next inside the For Each row only place Write Range activity and configure as below

Hope this may help you

Thanks

1 Like

Hi @Dorothy_lee !

The techniques of @lakshman and @Srini84 are pretty good ones !
Here is also another suggestion: separate-1-sheet-to-several-columns-based-on-condition.xaml (8.3 KB)

Let us know if it does not work as expected, or if you need further information on the codes :grinning_face_with_smiling_eyes:

1 Like

@Hiba_B Hi ! I’ll try your suggestion !
In the mean time, if there any way that I can validate whether there is 9998(example) account is existed or not? before separate sheets

Since we have 9 account list but not always each accounts has In/Out log , so sometimes there are some missing account. In this case, I’d like to validate account exist in first place and do not create sheet(I guess If condition…?)

1 Like

@Dorothy_lee Yes, you can validate before if in the table there is a row with that account number.

dtSheetRange.AsEnumerable.Any(Function(row) row("Account").ToString.Contains("9998"))

dtSheetRange: the DataTable with the range read.

At the end this will answer a boolean, true or false.

1 Like

Yes, technically it is possible as @JesusFontalvoGomez said.
It’s as you said Dorothy: you can add an if, and that’s it :wink:

@Hiba_B Hello !
I’ve just confirmed it’s working fine with test file but actually, Account format is a bit different with actual data.
So, below are account format from real data.
Could you please modify this part script? Sorry for inconvenience Thank you !!!

[Account Format sample: xxx-xxxxxxx-xxxx ]
100-910023-62304
196-910018-68104
201-910017-27804
140-007-832180
100-024-048485test file.xlsx (11.0 KB)

(From item In MasterDT_LOCAL.DefaultView.ToTable(true,“Account”).AsEnumerable() Select item.Field(Of String)(0).ToString.Split(Cchar(“-”))(1)).ToList()

with current script, it shows below error message.

Sure !
The error means that there are some account numbers that don’t have “-”: do you have some ? If yes we need to change a little bit the code so we take into account this use case.

In 100-910023-62304, do you need 910023-62304 or 62304 or 910023 ?

  • To have 910023 use
    (From item In MasterDT_LOCAL.DefaultView.ToTable(true,“Account”).AsEnumerable() Select item.Field(Of String)(0).ToString.Split(Cchar("-"))(1)).ToList()

  • To have 62304 use
    (From item In MasterDT_LOCAL.DefaultView.ToTable(true,“Account”).AsEnumerable() Select item.Field(Of String)(0).ToString.Split(Cchar("-"))(2)).ToList()

  • To have 910023-62304 use
    (From item In MasterDT_LOCAL.DefaultView.ToTable(true,“Account”).AsEnumerable() Select item.Field(Of String)(0).ToString.Split(Cchar("-"))(1)+item.Field(Of String)(0).ToString.Split(Cchar("-"))(2)).ToList()

1 Like

Thank you for giving quick reply !

All account has “-”

I need 100-910023-62304 which is full account number.

For the name of the sheet you need 100-910023-62304 ? I thought of 910023-62304 or 62304 or 910023

If you need 100-910023-62304 then
(From item In MasterDT_LOCAL.DefaultView.ToTable(true,“Account”).AsEnumerable() Select item.Field(Of String)(0).ToString).ToList()

@Hiba_B
“message”: “Get a list of the account numbers: Object reference not set to an instance of an object.”,
“level”: “Error”,


Did I input the script in wrong way…?

A that’s because the variable list_of_account_number is not initialized I think.
In the variable panel, be sure to initialize it this way: new List(of String)
image

Humm… It’s correctly set. I’ll delete it and recreate it

Oh sorry I sent you the wrong request, here is the right one:

(From item In DT.DefaultView.ToTable(true,"Account").AsEnumerable() Select item.Field(Of String)(0)).ToList()
1 Like

@Hiba_B Hello,
with your code, it’s getting proper sheet name and succeeded to separate sheets !
By the way, it’s getting below error which was not appeared from your initial .xaml file
Error occurs once excel work is completed.
Is this error occurs since for each row activity is in IF condition?


separate-1-sheet-to-several-columns-based-on-condition.xaml (12.8 KB)
Account_LOCAL.xlsx (12.3 KB)

Hi @Dorothy_lee !

Yes: it’s because in this file, in Sheet1, in lines 46, 51 and 52, the Account number is considered to be empty (“”). So when Excel tries to create an empty sheet, there is a problem: it’s not possible to have empty sheets, and it’s not possible to try to write in an empty sheet.

To solve the problem, we just have at the beginning to filter data to not take into account the empty account numbers, and here is the result: separate-1-sheet-to-several-columns-based-on-condition.xaml (13.0 KB)

1 Like

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