Automate task in excel

[Andrescaso]

Hi

I created a simple flow in power automate, but I don´t get one step

this is the flow

1) search a file in a folder

2) copy this file in an excel file

3) Add a header column in the first blank space

4) when I try to fill this new column when the same value from the row 2 to the row N, only I get fill one cell,

This is an excel example

please help me

Regards 

[csundar]

@Andrescaso  

You likely need to create a loop and iterate through the number of rows (from row 2 through FirstFreeRow-1)

Use the Get first free column/row action to get the first free row.  

Create a variable called CurrentRow and set it to 2 (your first row).

Loop the action you're doing until CurrentRow < FirstFreeRow.  Make sure that you're using the CurrentRow as your row reference in the action.  Make sure at the end of your loop you increment your current row by 1.

If this doesn't work, please share details of the flow you're created so we can better assist.

[Andrescaso]

Thanks @csundar 

This method work, but take too much time for execute, the files have around 10.000 rows each one.

For solved the problem, I copied a column with the same value in all the rows, use the replace function and write function to add the new column.

now i want to make a sum at one column by filter for a specific value in other column, this is posible?

Regards

[csundar]

You might be able to achieve the same using the following

Copy row 2 cell

select range Row 3:FirstFreeRow-1

Paste on currently active cell

I'm just reading through documentation but looks like it should work.  This would get rid of the loop and delay.

If you format as a table you also might be able to use the Write to Excel function.  

For your 'sum' question you should be able to use the Write to Excel to write the function you want to put in the cell %"=sum(...."%

[Andrescaso]

Thanks @csundar 

If you format as a table you also might be able to use the Write to Excel function.  

How I doing this?

For your 'sum' question you should be able to use the Write to Excel to write the function you want to put in the cell %"=sum(...."%

I try this, but excel take this like a text not a formula, i miss a step?

Regards

[csundar]

Format as table has to be done in the Excel file itself.  If you're starting with a clean file every run and adding the information in I'd recommend creating a template file with the table you want and then use the File actions to create a copy for the run you're doing this.  The format as table is found on the home tab.  Excel will then automatically extend the table as you add records.

To the sum function.  Once you create this as a table, try to use the total row (in table design)

This may allow you to create the sum function separate from the rows.  I have not tried this yet but should work.  If not you may need to do one additional interim step of inserting a row at the bottom of the table to add your new items.

[Andrescaso]

Hi @csundar 

I format a template excel in table format, paste the .txt in the Excel (all the fows and columns have the table format, the only thing i'm disturbing is the loss of the name headers)

I insert a new column in the table

Write the date in tha row 2

copy this date

select the Row 3 to 10417

Paste the copy cell

but only paste in one cell, also I try with the write function with the same result

And the total row, only gives the count of row, not the sum

I think the best option is write a formula in a blank cell, but when I write the formula excel take like a text and not a formula, if after of execute the flow I do double click and  enter in the cell when I write the formula, Excel convert this cell in a formula, this step is the one I miss

Regards

[Andrescaso]

An update, if I write in a row the date with the equal symbol (=15/12/2020), this paste the same value in all the rows of the column, this solve my first problem, paste the same date value in all the rows of a column

But with this method i loss all my headers name

and if i put a function to write in the excel like =sum(A2:A4) the excel still consider like a text and not like a formula

Regards