Excel background process

Hi all,

Is there any better way to do data massaging in excel without using keystrokes?

Currently I have more than 10k data in excel. Require a lot of data massaging for every column i.e. more than 10 columns. For example first column I need to calculate length of character. The next column I need to calculate Number of days etc. After I run the whole process it took me more than an hour to complete everything. Because a lot of for each row activity. (This is how I massage the data for every column).

I try to avoid opening the excel and use click activity/keystrokes activity which maybe took me lesser time to complete.

I really need other opinion on this. Thanks

@jenkim

You can do that using datatable

place the ReadRange activity and it will create Datatable with your data

after that you can use Foreach row and put your logic, so it will work for each row according to the logic

Hope this helps

Thanks

1 Like

That’s what I did. My only problem is the time taken for it to complete. It took me 1 hour ++ to complete the whole process of data massaging.

Back to my question, is there any better way to do it?

1 Like

@jenkim Can you tell us what is the output you need ? Maybe with some sample excel files you can show it?

1 Like

This is the example of data massaging that I did in excel. Which is to calculate the number of days.

image

If a normal human way : I will put a formula at column I - “=H2 - F2”. To get the number of days.

In my process I use read range activity and for each row activity to calculate the number of days which is taking a lot of time to finish the whole process since the data have more than 10 000 data.

My only problem is the time taken. If anyone have a better suggestion.