Excel Column values re arrange

Help Activities
, ,
1

Hi Everyone, I have a scenario where i need to re arrange my excel columns. The data is misplaced in some columns i want to re arrange those columns.

I have few columns, example D & E columns. E column have some numeric values and I want E column total but the total value is available in D column end i want to copy and paste in E column.

Pease have look at the excel attachment below for your better understanding

Note : This is a System generated Excel.

I’m attaching my sample excel below for reference. It is having both input and Output.

Coumn re arrange.xlsx (9.8 KB)

2

@Learner007

In all the scenarios the total is only misplaced or are there more column?

Cheers

3

Hi @Anil_G in D column also. It’s not about only last column but sometimes we may get in between also , suppose if i take D & E columns. It have 5 rows. 1 row value is in D column and rest in E column. How to handle this kind.

4

@Learner007

Are the columns where the data is misplaced are empty always?

If yes we can use that and find the misplaced data

If not we need to correct at the source only to check what is wrong and download in proper foemat

Cheers

5

@Anil_G i can’t correct this is source file because it’s auto generated

Screenshot_2023-08-29-09-54-24-075_cn.wps.moffice_eng-edit
Screenshot_2023-08-29-09-54-24-075_cn.wps.moffice_eng-edit1006×588 70 KB

If you see this screenshot value column in empty and it is having only 1 row data and i want to move that to value 2 after moving that value i need to delete the VALUE COLUMN.
Even stock column also it is having 1 row data i want to move that to stock record column and after that i need to delete STOCK COLUMN.

6

@Learner007

Two things here…if always the data is misplaced in value and stocks columns only and if it needs to be added at the end or at the last blank space we can do that …

Try below logic

  1. Use a index variable for index property in for loop
    2 Use for each row in excel activity
  2. Inside the loop use if Not String.IsNullOrEmpty(currentRow.ByField("Value").StringValue)
  3. On then side use write cell activity with value as currentRow.ByField("Value").StringValue and range as Excel.Sheet("SheetName").Cell("F" + If(String.IsNullOrEmpty(currentRow.ByField("Value1").StringValue),index,index+1).ToString)

Similarly repeat for other column also

Cheers

7

Sure @Anil_G i will try this once

1 Like
8

@Vikas_M if you can, can you help on this is there any option of LINQ query for this or any alternate.