Repeat the same actions for different excel files

Hi team!

I'm working on a project where I get data in the exact same excel format, and in order to process that data I need to perform the exact same steps to each files before I can analyse it. So for instance, for each file I need to hide column C, E, F and G - then I need to filter column D - then I need to sort by descending date from column A and time from column B - etc etc... This doesn't seem very efficient to have to do over and over for each separate file that I get, so I was wondering if there was a way in excel to save all these actions and apply to a file when I open it?

Thanks in advance!!

Cheers,
Titia

Thank you TMS, excellent idea, I had no idea of macro's yet.

I've now tried it several times, and sometimes it works perfectly. However, mostly it come up with Run-time error '9': Subscript out of range.
So even though every excel will has the exact same format, the length of the columns differs immensely, would that be the issue here?

Absolutely brilliant!! All excellent advice and I've tried following it, however some commands just don't seem to work (maybe with my version of Excel?)
For instance it doesn't rename the sheet with this command - Sheet1.Name = "Disloe" ,and the LastRow thing also keeps giving errors so I just opted to go with Range("A:A") etc. Bit sloppy but does the job good enough for me to get the data I need. This is my scribble now (almost no idea what I'm doing, so please be gentle) , please shout if you see big red flags

Please Login or Register  to view this content.

Ok so on the same topic, working with the same data, I was wondering if I can take these wonderful macros a step further?

I've attached how the original data looks and what I'm trying to get out of it. I need to know the number of long tails recorded per night, which to me seems immensely complicated because there's a change in date during the night and the night sometimes doesn't finish until 7am. I already added the extra column H with a value of 1 next to it, which to me seemed like the easiest way to have excel calculate the sum when I manually select the relevant cells. But it still a lot of work going through all the rows, and has a high rate of human error...

Not sure if I need to open a new topic for this, please correct me if I do! And again, thank you to everyone willing to give this a look, so far this forum has been extremely helpful to me.

Hi,

what is the date format within your text files in your last attachment ?

What is the criteria to reorder the source data to the expected result ?

Hi Marc,

I believe the date format it dd/mm/yyyy
I hope I'm understanding/answering your question correctly:

• Order on date
• Order on time
• filter out the rows that don't have "Long tail" in column D
• hide column C, E, F,G
• ad a "1" to all remaining rows in column H (so all the rows with Long tail)
• provide a sum off all the "1" in column H that correspond with a 24h period (starting in the evening (PM) and ending in the morning of the next day (AM)

Does this make sense and is this doable in excel?

Thanks advance! Again!

Also, would it help if I convert this file to an excel file instead of csv? And would that results in data loss?

It depends on the source and the destination files as your title states for Excel files so .xlsx but this is csv text files within your attachment ?

And your post #9 explanation does not match your post #7 attachment so difficult to help
or you are enough confident with your Excel / VBA skills to amend any starting point code any helper can share ? …

Oh so sorry, I don't think I can change the title now. The files are always provided in a .csv format, and I don't really care what file it is in the end as long as I get the data out of it that I need.

I'm not sure how the criteria to reorder the source data to the expected result are really relevant for what I need to explain what I'm after though.
If you have a look at the WC1-A result.csv‎ file and apply this macro:

Please Login or Register  to view this content.

You can see that in I116 and I186 there's a number relating to the sum of the number of "1" in column H. Normally these files are way bigger and have more days, and it takes me quite long time to scroll through and select the cells that are within that 24h period without make mistakes.
I'm looking for a way for excel to do that for me, and provides me with the sum off the "1" in column H per day, which is a 24h period starting in the pm and finishing in the am.

Please let me know if that makes sense? It's so hard to explain

Ok if a source csv text file can contain more than a day then attach such csv text file and accordingly its exact expected resut workbook
and choose if the final result file is an Excel workbook or a csv text file …

I'm sorry Marc, I feel we're on way too different levels here and I can hardly understand what it is you want from me to enable you to help me.

I'm going to open a new threat on this issue to encourage more advice and different perspectives, but I really appreciate the help you've offered so far. I've learned a lot!