How to combine columns from multiple workbooks?

[blenderwala]

I am working on a Classroom Files.


Where i have many files containing report of each test like Test 1, Test 2, Test 3...
I have to make a Master workbook that will contain the Mark Columns from All the Test Files


I tried the PowerQuery ( I am very much new to PQ) but failed as it was repeating the Name of the Students
I Just Need to Create A Master Workbook that will have Name of the Students and Marks of Each Test
Is there any Way to do this using PowerQuery or VBA

Thank You

 

[vw412]

what you want is probably doable but would be helpful if you could upload to dropbox a couple sample files and a sample master file so we could test a solution against meaningful data

 

[blenderwala]

Hi @vw412

Thank you for your response


I attached a link of those Files here

Combine Column Error/Master File.xlsx and 5 more files

6 files sent via WeTransfer, the simplest way to send your files around the world

we.tl

I just need to copy Mark's column from all test files to the master file. There are lots of files hence i can not use VLOOKUP.
It would be very helpful if you guide me to do this with Power Query or VBA


Thanks

 

[vw412]

I got the files. Below is some thoughts I have for this VBA project.

'
' Merge multiple source workbooks(worksheets) into one master file
'
' Source files consist of two columns: Name and TestScores
'
' Master file has one Name column and as many TestScores columns as source files
'
' Allow merging all files in a folder or selected files from a folder.  Selected files could be 1 or many
'
' When merging must match Name in each row of Master to Name in each row of Source.
' If Name not found in Master ask if needs added or skipped.
'
' Use source file name for TestScores column name in Master.

Please review these and comment.

 

[blenderwala]

@Hi @vw412

Yes ! This is exactly what i want to achieve.

VBA would work but any thoughts on PowerQuery?


Thanks

 

[vw412]

Unfortunately I do not know PowerQuery or how to use it.

 

[blenderwala]

Hi @vw412

it's perfectly fine with VBA. I was just curious about PowerQuery

Thanks for your response.