How to import multiple text files in its own unique column

[FlyanRyan]

Hi everyone,

I have multiple text files that contain a single column of data, and I would like to import the files in its own column.

For example:
File A name occupies cell A1 and the data occupies from cell A2 onwards down the column
File B name occupies cell B2 and the data occupies from cell B2 down the column, etc.

I've looked into power query, but it just combines all the data from the text files into 2 columns.

Any guidance and help would be appreciated! Thank you all in advance!

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

I have no idea what Excel version "2201" is supposed to be. Pleasse update to tell us the EXCEL version you are using... and if its for PC or Mac.

[FlyanRyan]

Hi Glenn,

Sorry about that, I'm new to the forum.
I have now updated my excel version.
I am currently using PC and have attached a small excel sheet in my first post that describes what I want and where I'm stuck at in two separate tabs.

Best regards,
Ryan

[JeteMc]

I could not find a way to pivot the proper data set in columns A:B on the try with power query sheet using Power Query, so I used formulas.
The formula for the column headings (E3:H3) is: =IFERROR(INDEX($A2:$A61,MATCH(0,INDEX(COUNTIF($D3:D3,$A2:$A61),,),)),"")
The formula for the values from Column1 is: =IFERROR(INDEX($B$2:$B$61,AGGREGATE(15,6,(ROW($B$2:$B$61)-ROW($B$1))/($A$2:$A$61=E$3),ROWS(E$4:E4))),"")
Note that I feel there are Excel 365 version dynamic array formulas that will accomplish the entire process, so perhaps someone who has access to those will help.
Note that for making further calculations using the values in Column1 the proper data set in columns A:B will likely be more useful than the matrix in columns E:H.
Let us know if you have any questions.