Adding new Column and applying formula in excel

[syed]

Hi ,

I have source file which has around 500 records before reading entire excel into collection I want to add one column called filter key and need apply formula for that column like = concat("I1","J1"). How i do this ? I am new to BP and I am from Java background. So, if anything need to achieved using VB or C# kindly show piece of sample codes and dll needs to be added.

 

[VJR]

Hi syed,

You can do this without a code stage as follows.
- Use Set Cell value to set the header for the new column
- Then set the desired formula just in the first cell.
The $ sign is to keep the column fixed but to keep changing the rows
View attachment 1532365887913.png

- Find the last row of the sheet using 'Get Number of Rows' action of Excel VBO

- Then Paste the first cell C2 from C3 to the last row

View attachment 1532365981309.png

- Final output after running the process
View attachment 1532365846374.png

 

[syed]

Hi,

Thanks for your reply. Formula part i understand. One more question how can Add new colmun in excel. As of now i am use oledb. Is there any other way?

 

[VJR]

Hi syed,

What is the data of the new column?
Where is it coming from?
Or is it just a column name with no data in it initially?

 

[syed]

There is a master file which blue prism will download from one of shared site which has 500 records. Once bp dowloaded the Master file before reading the data into collection, I need to add one column called "filter key" which has formula like =CONCAT("H2","I2") to generate unique key for each row so that it will be easy for data manipulation.

As of now the master file has that filter key column with required formula. But now the end user has decided to remove that column. So, we need to add it before data manipulation starts.

 

[VJR]

There is a master file which blue prism will download from one of shared site which has 500 records. Once bp dowloaded the Master file before reading the data into collection, I need to add one column called "filter key" which has formula like =CONCAT("H2","I2") to generate unique key for each row so that it will be easy for data manipulation.

As of now the master file has that filter key column with required formula. But now the end user has decided to remove that column. So, we need to add it before data manipulation starts.

The instructions I have provided above is for the same

- Use Set Cell value to set the header for the new column
- Then set the desired formula just in the first cell.

Simply set the header of the new column in the desired cell with whatever you want eg; "Filter Key"
Then use the concatenate formula as explained earlier.

 

[syed]

Thjanks VJR it worked

 

[Bharathwaj]

Hello,

There is a number in cell1 and in cell2 i want to do calculation (=c1*10%). How can i achieve it using "Set cell value"?