Combining data - different workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-2535694%22%20slang%3D%22en-US%22%3ECombining%20data%20-%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20a%20series%20of%20near%20identical%20spreadsheets%20in%20folder%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3Ec%3A%5Cdocuments%5Crefunds%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bwith%20various%20titles%20which%20change%20every%20week.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EEach%20file%20has%20a%20single%20row%20of%20values%20A2%3AD2%20on%20worksheet%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%5BSheet1%5D%3C%2FFONT%3E%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%3ECan%20I%20produce%20a%20spreadsheet%20in%20a%20different%20folder%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3Ec%3A%5Cweekly%20refunds%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bthat%20contains%20this%20row%20of%20data%20only%20from%20every%20file%20in%20the%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%5Crefunds%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3Bfolder%2C%20ie%20a%20varying%20number%20of%20rows.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20attach%20sample%20files%2C%20the%20weekly%20refunds%20one%20would%20be%20in%20a%20different%20folder%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2535694%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2535712%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20data%20-%20different%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2535712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100540%22%20target%3D%22_blank%22%3E%40David0424%3C%2FA%3E%26nbsp%3BProvided%20you%20are%20Not%20on%20a%20Mac%2C%20look%20into%20PowerQuery%20(PQ).%20Get%20Data%2C%20From%20File%2C%20From%20Folder.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChapter%209%20in%20the%20link%20below%20(scoll%20down%20a%20bit%20on%20the%20web%20page)%2C%20is%20just%20one%20of%20many%20on-line%20resources%20that%20describes%20how%20to%20do%20just%20that.%20If%20you%20are%20unfamiliar%20with%20PQ%2C%20I'd%20start%20at%20the%20top%20to%20acquaint%20yourself%20with%20the%20basics.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceloffthegrid.com%2Fpower-query-introduction%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a series of near identical spreadsheets in folder c:\documents\refunds with various titles which change every week.
Each file has a single row of values A2:D2 on worksheet [Sheet1]
Can I produce a spreadsheet in a different folder c:\weekly refunds that contains this row of data only from every file in the \refunds folder, ie a varying number of rows.

 

I attach sample files, the weekly refunds one would be in a different folder

1 Reply

@David0424 Provided you are Not on a Mac, look into PowerQuery (PQ). Get Data, From File, From Folder. 

 

Chapter 9 in the link below (scoll down a bit on the web page), is just one of many on-line resources that describes how to do just that. If you are unfamiliar with PQ, I'd start at the top to acquaint yourself with the basics.

 

https://exceloffthegrid.com/power-query-introduction/