CODEWITHSUNDEEP
excelmergepowerquerytranspose
KKR
KKR

Reputation: 79

Combine and transpose multiple excel files

I have multiple xls files with the same format. There are only two columns with data and other empty columns (C to I) which I do not need. The first column data is the same for all files and I need to get the second column data from multiple files into their own column in my final file. My output file should look like the below. I tried power query but I am not successful in doing it right. Any help is appreciated.

Input file format: enter image description here

Output file format:

Column A FileName1 FileName

Upvotes: 0

Views: 298

Answers (2)

horseyride
horseyride

Reputation: 21318

A powerquery version. Edited from original to keep only first two columns from each file

let Source = Folder.Files("C:\directory\"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind","Sheet"}),
#"Remove top 2 rows" = Table.TransformColumns(#"Removed Columns",{{"Data", each Table.Skip(Table.DemoteHeaders(_),3), type table}}),
#"Keep 2 columns only" = Table.TransformColumns(#"Remove top 2 rows",{{"Data", each Table.SelectColumns(_,List.FirstN(Table.ColumnNames(Table.DemoteHeaders(_)),2)), type table}}),
List = List.Union(List.Transform(#"Keep 2 columns only"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Keep 2 columns only", "Data", List,List),
#"Pivoted Column" = Table.Pivot(#"Expanded Data", List.Distinct(#"Expanded Data"[Name]), "Name", "Column2")
in  #"Pivoted Column"

Upvotes: 1

p._phidot_
p._phidot_

Reputation: 1950

Assuming when the paste link (in B2 of Output File) is done, the link look look like this : =[File1.xls]Sheet1!$B$4

Change the link a bit to :

  1. make the "File1.xls" text changeable depending on B1 values (B1 contains file name, as per figure2)
  2. Not displaying '0' if there is no data.
  3. 'Lock' the B column reference, but make the row dynamic (source data is row 4 onward).

[The process]

[1] : use INDIRECT()

[2] : use IF(formula="","",formula)

[3] : remove and apply $ at the right place

After [1] : =INDIRECT("["&B$1&"]Sheet1!$B$4")

After [2] : =IF(INDIRECT("["&B$1&"]Sheet1!$B$4")="","",INDIRECT("["&B$1&"]Sheet1!$B$4")

After [3] : =IF(INDIRECT("["&B$1&"]Sheet1!$B4")="","",INDIRECT("["&B$1&"]Sheet1!$B4")

Notice that in the indirect B$1 is used. So that for after copy paste, in column C, it'll become C$1 (refer to File2.xls, not File1.xls)

[Solution]

In cell B1 of the output file, put the filename.

In cell B2 , put =IF(INDIRECT("["&B$1&"]Sheet1!$B4")="","",INDIRECT("["&B$1&"]Sheet1!$B4") (I'm assuming the Sheetname is Sheet1, please edit depending on how your paste link look like.)

Drag B2 downwards. If cell C1 (and other column) have of the filename, just copy B2++ cells to C2++ cells, the formula will adjust accordingly.

Please share if it works/not.

Upvotes: 0

Related Questions