Automatically edit/tidy a worksheet and create new worksheets based on a filtered column.

[john36f]

I have 100s of Excel files that contain a worksheet with 1000s of rows. Each row has 50+ columns. The columns in each Excel file are exactly the same. As an example, the columns are:

Date, Place, Item, ValueHeading1, Value1, ValueJeading2, Value2, ValueHeading3, Value3….etc.

My first problem is that I do not want a column for each ‘ValueHeading’ but I do want to label the top row of the following column with that ‘ValueHeading’ and then delete the previous column.

I can do this very easily manually, but I want to know whether there is a more efficient way to do it automatically.

Secondly, the rows are in chronological order (by seconds descending) but there are x amount of items per Excel file. The x varies from file to file but there is a static variable in each file that states the Number of Items (x).

Basically, I want to create x amount of new worksheets in each file that filter the rows in ‘Sheet1’ by ‘Item Name’. Once again, I can do this manually, but as I have 1000s of Excel files I am trying to find a way that can do this automatically.

I have attached a screenshot of a sample spreadsheet to give an indication of what I have.

I would be very grateful if anyone could suggest a way of resolving my problem. I am not too familiar with Macros, but if that is what is needed, I am prepared to learn. But I initially want to check whether a macro is the way forward as a friend has suggested I use MS Query instead, but I have no idea.

Many thanks,
John

[JeteMc]

Hello john36f and Welcome to Excel Forum.
Do you have Power Query which is a free add-in for the 2010 version of Excel?
I ask because someone may be able to use Power Query to transform the Excel files such that they use Seconds, Number of Items, Value 1, Value 2 ... as column headers for the actual data.
Have a look at this video and see if this might be an option for producing the new worksheets: https://www.youtube.com/watch?v=sjnUThSQ8hk
Note that using this method I don't believe that there would be a column of Item names as the Item field is used as a filter.
If the above seems like an option then please utilize the instructions in the banner at the top of the page to upload the .xlsx file from which the screenshot in post #1 was made.
Let us know if you have any questions.

[john36f]

Hi JeteMc,

Thanks very much for the response. In the end, I was able to resolve my problem by using a combination of WinAutomation and an excel Macro. This transpired to be the best solution as I was able to also perform a significant number of other planned tasks on the same spreadsheet during the same automation process. It taken some working out, but I got there in the end. Thanks again. Best wishes, John

[JeteMc]

Thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.