Forum Discussion
Merge cells from different Excel Workbooks
Hello,
I hope you can help?
I have set up a excel workbook to gather feedback on students from Industrial supervisors. Once i get all the spreadsheets back (could be 50+) I want to merge certain cells together (E3,E5, L18 and L39) from one spreadsheet in the workbook and i want to do the same for multiple workbooks. The data will be in the same location on each spreasheet in each workbook.The four cells has the student name,student no, tasks mark and duties mark. I tried to use Power Query but it seems to be bring everything in and it looks really messy as i have my spreadsheet designed and dont have the standard row 1: headings and row 2 data. Any help would be very much appreicated.
Ideally the cells that you're interested in would be named ranges (see more here:
https://support.microsoft.com/en-us/office/create-a-named-range-from-selected-cells-in-a-worksheet-fd8905ed-1130-4cca-9bb0-ad02b7e594fd)when connecting to such Excel Power Query will recognize the ranges the same way it recognizes data tables and sheets
Otherwise, if the structure is static you would need to create a logic to get to the specific cell from the messy input, but that's a bit ugly solution.
As for the multiple files, you can connect to folder (remember to exclude subfolders if needed), and use single extraction logic for all of them
3 Replies
Ideally the cells that you're interested in would be named ranges (see more here:
https://support.microsoft.com/en-us/office/create-a-named-range-from-selected-cells-in-a-worksheet-fd8905ed-1130-4cca-9bb0-ad02b7e594fd)when connecting to such Excel Power Query will recognize the ranges the same way it recognizes data tables and sheets
Otherwise, if the structure is static you would need to create a logic to get to the specific cell from the messy input, but that's a bit ugly solution.
As for the multiple files, you can connect to folder (remember to exclude subfolders if needed), and use single extraction logic for all of them
Thanks very much for your help. I did use power query in the end and once i formatted my data as a table, power query was able to pull that data out from each spreadsheet.
Thank you very much for your help, i will try what you have suggested.
