Write multiple Cell in Excel

Help Studio
, ,
1

Hi,
I need help to write multiple cells in an Excel based on a key value. In the below example, I need to write status as “Active” for all the accounts that is 131. Input for Write cell is only the cell number. How to write for multiple rows that has Account number as 131.

For eg,
Name Account Status
James 131 Active
John 132
Alex 131 Active
Max 131 Active

2

Hi @besakkiappan46 ,

Maybe an approach would be to Read the Data as a Datatable and perform the update of the Column value using For Each Row activity and then Update the Excel sheet with the Updated Datatable value.

Also, a Similar in looping would be to increment the row number in Range property of Write Cell Activity. So that a direct update for each row in Excel is performed.

You could use a Formula as well to update the Column Values. (A Combination of Write Cell and Auto Fill Range).

Let us know if you have a specific requirement to write the values to the Excel sheet directly or we would prefer to go about the Datatable method of updating.

3

I’ll have to update the “Status” column for each transaction. So, we are looking at “Write Cell” directly in the excel instead of updating it as a datatable.
but for every transaction when we update the status, we might have to update Multiple cells if the Account number is duplicate

4

I could increment the row number if I already have filtered rows that contains only Account number as 131. But In my case, I didn’t filter since I’m not updating datatable instead using Write cell to write the excel directly. Also, I may have a total of 5000 records.
Is there an Option to filter the data in excel based on Account number and then I can use increment to write cells.

5

@besakkiappan46 ,

As we understand it is a Transaction based update that you would required to follow, then having an Index for the Transaction (Or Transaction Number if RE Framework is used), could be used as the Row Number for the Write Cell activity. You could then apply you condition in If Activity like :

AccountColValue.Equals("131")

If the above is true, Update Status Column using Write Cell.

6

@besakkiappan46

You can also try with assign activity inside if

If activity condition as

TransactionItem(“AccountNumber”)=“131”

Then

TransactionItem(“status”)=“Active”

In the end process use write range activity

It will get updated

7

Not sure why do i need Transaction number for getting the row number? transaction number will just act as a counter. Not sure how it’ll be handy during Write cell. In the below Image, I need to update cells J10,J12 and J14 for a single transaction (I.e. Account Number 131) .

image
image703×342 4.22 KB

8

I need to update multiple cells for each transaction. If my Account number has 10 records with value as 131. I need to update 10 cells.

9

@besakkiappan46

can you provide what was your transaction item is a datarow or any other

10

It is a String variable

11

@besakkiappan46 ,

Could you let us know what is the Current Design or Implementation done for processing the Transactions ?

12

Account Number is part of Queue Item . Each transactions will be having different Account Number. For each transaction, I need to update the corresponding “Status” column in the excel.But the only Issue is, The account number has duplicates in the excel. So, I need to update all the status for all the rows that has the Current Account Number. Upon updating the status, I’ll move to next account number (From queue)

I could use filter data table, get the records for Current account number, update the status and Update the data table. But I don’t want to use Datatable. But instead write directly to excel.