Seperate datatable into different worksheets

Help
1

Hi All,

I have a excel containing many different ID, how can I put each ID into a new sheet?

I have attached excel for reference, sheet 1 is input and other sheets are expected result.

Please noticed that actually I won’t know how many ID will be contained in sheet 1, therefore I wont know how many worksheets should I create.

In case, maybe finally it only contains 2 different ID or contains 100 different ID.

Many thanks.testing.xlsx (13.7 KB)

2

Steps:

  1. Read Range Activity to read the data from Sheet1, saved in DataTable DT1
  2. Integer Variable for setting the Sheet names, say intCounter (default value = 1)
  3. For Each Loop Activity
         for each row in DT1
             DataTable variable, Assign Activity DT2 = new DataTable
            Assign Activity, DT2 = row.CopyToDataTable
            Assign Activity, intCounter = intCounter + 1
            Write Range Activity, Name: “Sheet” + intCounter.ToString
                                              Range: “A1”
                                              DataTable: DT2
3

Thanks but error occur in assigning DT2 = row.CopyToDataTable
ÇopyToDataTable’is not a member of 'System.Data.DataRow

4

Also I understand your concept but each ID may appear in many rows. Therefore we need to set condition to compare the value of first column

5
  1. Add a row to DT1 with headers for filtering or add headers in the excel file directly
  2. For Each Loop Activity
         for each row in DT1
            Assign Activity, intLookUpId = row(1)
            Filter Data Table Activity, Rows Filtering Mode, Keep
                                              Column: “Id”
                                              Value: intLookUpId
                                              Output DataTable: DT2
            Assign Activity, intCounter = intCounter + 1
            Write Range Activity, Name: “Sheet” + intCounter.ToString
                                              Range: “A1”
                                              DataTable: DT2
6

Hi @raymondhui

Can you check this . It may useful to copy data and convert to data table.

Regards
Balareva