Hi,
I have 6000 data files in .txt format organized in two collumns with around 3500 rows each.
I want to import all the data in a single excel file, the best way I saw was using the Power Query and importing the data from a directory. Importing the data, and filtering the rows and columns, so only data is selcted and nothing else in the files worked like it should be, but the thing is, I don't want all my data in two long collums
In a perfect scenario I would like something like:
Filename1 | Data from File 1 (Fist Collumn) ->
| Data from File 1 (Second Collumn) ->
Filename2 | Data from File 2 ->
| Data from File 2 ->
...
and so on
So transpose the data per File, and then add them from top to bottom. Perfectly shiftet by one to the right, with the filename in the first empty collumn.
I get how to transpose all the data, but how would I only transpose data per file and then add them under each other, or transpose everything and move the data after 3500 Collumns (always the same amount of data) to the front two rows down.
Sadly I can't upload an Attachment right now, since I don't have access to the files right now, I hope you still can help.
Greetings
Voigt
Hi Voigt,
Excel doesn't have enough columns for you to format it like this. That said, it sounds like what you need to do is bring in all of the data in a tabular format and then use a PivotTable to create the report structure you want.
It's difficult to help much more without some sample files and a mock up of how you want the final result to look.
Mynda
