I need to sort my data in excel according to column for example if i have the data in A column it should be in A column and level 1 if B column put the data in A column and level 2, if C column put data in A column and level 3 and so on so forth, (Level column will be created in B column after the filtration)
Could u help me with that

HI @marwan992,

Here are the steps:

  1. Read your excel and store in the data table.
  2. Then use if condition to check which column has the data
  3. After detecting the data, copy the entire column data to column A
  4. Then use sort data table activity to sort the data

Hope this helps

1 Like

how can i copy the whole data into A column ??

If you have less data, you can use for each row to write data from one row to another, If you have huge data, it will consume more time which is not the objective of automation.

So, what we can do is…

  1. After detecting the data column, use sort data table on that column itself

  2. Write the data table to excel using write range activity within the excel application scope.

  3. Then, as we have the column index we can do that using Invoke code activity and here is the code which will help you

     Dim excel As Microsoft.Office.Interop.Excel.Application
     Dim wb As Microsoft.Office.Interop.Excel.Workbook
     Dim ws As Microsoft.Office.Interop.Excel.Worksheet
     excel = New Microsoft.Office.Interop.Excel.Application
    wb = excel.Workbooks.Open("FileName", [ReadOnly]:=False)
     excel.Visible = True
     ws = CType(wb.Sheets("Sheet Name"), Microsoft.Office.Interop.Excel.Worksheet) 
    Dim UsedRange As Integer = ws.UsedRange.Rows.Count
       Dim str As String = Convert.ToChar(64 + "Column number")
    ws.Range(str + "2:" + str + (UsedRange).ToString).Copy()

You can do even sorting in the code as well… But better to go with the activities :slight_smile:

1 Like