Home

Combining multiple quarterly workbooks into one master workbook.

%3CLINGO-SUB%20id%3D%22lingo-sub-765340%22%20slang%3D%22en-US%22%3ECombining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765340%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20get%20quarterly%20excel%20files%20that%20I%20have%20been%20manually%20combining%20and%20I%20am%20wondering%20if%20there%20is%20a%20more%20efficient%20way%20that%20I%20can%20be%20doing%20it%20with%20less%20chance%20of%20user%20error%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20quarter%20I%20get%20the%20following%20excel%20workbooks%3A%3C%2FP%3E%3COL%3E%3CLI%3EAn%20inpatient%20listing%20for%20my%20hospitals%3C%2FLI%3E%3CLI%3EA%20listing%20of%20inpatients%20who%20had%20a%20complication%3C%2FLI%3E%3CLI%3EA%20listing%20of%20inpatients%20who%20returned%20to%20the%20Emergency%20Department%20after%20discharge%3C%2FLI%3E%3CLI%3EA%20listing%20of%20inpatients%20who%20were%20readmitted%20within%2030%20days%20of%20discharge.%3C%2FLI%3E%3C%2FOL%3E%3CP%3ECurrently%2C%20each%20quarter%20I%20take%20the%20new%20data%20and%20append%20it%20to%20the%20bottom%20of%20a%20master%20excel%20workbook%20with%20a%20worksheet%20for%20each%20of%20the%20above%20books.%20This%20can%20be%20quite%20time%20consuming%20as%20occasionally%20columns%20are%20added%20into%20the%20quarterly%20reports%20so%20they%20don't%20line%20up%20with%20the%20columns%20of%20the%20master%2C%20and%20I%20have%20to%20adjust%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20in%20excel%20that%20I%20can%20append%20the%20rows%20from%20each%20new%20workbook%20to%20its%20corresponding%20sheet%20in%20the%20master%20where%20the%20data%20will%20be%20placed%20in%20the%20column%20with%20the%20same%20header%2C%20and%20in%20situations%20where%20there%20is%20no%20existing%20matching%20header%2C%20insert%20a%20new%20column%20with%20that%20header%20to%20the%20master%20file%3F%20This%20would%20leave%20a%20bunch%20of%20blanks%20in%20the%20previous%20rows%2C%20but%20that%20wouldn't%20really%20be%20an%20issue%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20guidance%20or%20suggestions%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBrian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-765340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-765699%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-765699%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Brian%3C%2FP%3E%3CP%3ETo%20my%20humble%20point%20of%20view%2C%20you%20have%202%20options%20(depending%20upon%20the%20structure%20of%20your%20files)%3C%2FP%3E%3CP%3E%3CSTRONG%3EOption%201%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECreating%20a%20Code%20in%20VBA%3C%2FP%3E%3CP%3EI%20am%20attaching%20an%20excel%20file%20with%20about%2090%20sheets%20and%20by%20triggering%20the%20code%20using%20the%20shortcut%20%3CSTRONG%3ECTRL%2B%20SHIFT%20%2B%20P%3C%2FSTRONG%3E%2C%20all%20the%20sheets%20are%20consolidated%20and%20appended%2C%20while%20the%20original%20sheets%20are%20deleted.%20(of%20course%20modifications%20can%20be%20done%20as%20needed%2C%20I%20am%20just%20showing%20you%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOption%202%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EUsing%20Power%20Query%20to%20append%20any%20number%20of%20files%20together%20if%20they%20were%20put%20in%20the%20same%20folder.%20All%20what%20you%20need%20to%20do%20with%20every%20new%20file%20is%20to%20refresh%20the%20Query.%3C%2FP%3E%3CP%3EHere%20is%20a%20link%20to%20a%20similar%20situation%20explained%20by%20Excel%20MVP%20Mike%20Girvin%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D_csX8sCzJd0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D_csX8sCzJd0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775905%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20answer!%20Macros%20seem%20to%20be%20blocked%20by%20my%20IT%20department%2C%20so%20I%20will%20see%20if%20I%20can%20make%20the%20power%20query%20method%20work%20-%20the%20tutorial%20you%20linked%20looks%20very%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20never%20used%20power%20query%20before%20-%20Will%20I%20be%20able%20to%20use%20it%20even%20if%20the%20columns%20are%20not%20consistent%3F%20As%20in%2C%20if%20there%20is%20a%20new%20column%20in%20the%20latest%20workbook%2C%20will%20it%20just%20create%20a%20new%20column%20in%20the%20file%20and%20have%20null%20values%20for%20the%20previous%20rows%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBrian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-775921%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-775921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Brian%3C%2FP%3E%3CP%3EAlthough%20Power%20Query%20is%20doing%20magic%2C%20however%2C%20at%20the%20end%20it%20just%20repeats%20the%20same%20steps%20in%20the%20same%20sequence%2C%20assuming%20that%20you%20run%20the%20function%20to%20similar%20sources.%3C%2FP%3E%3CP%3EAlso%20note%20that%20if%20you%20are%20recording%20among%20the%20steps%2C%20a%20step%20that%20keeps%203%20columns%20and%20Deletes%20other%20columns%20%26gt%3B%26gt%3B%20so%20it%20will%20delete%20Other%20columns%20whether%20they%20are%205%20or%206%20or%207...%3C%2FP%3E%3CP%3EOnce%20you%20get%20the%20concept%2C%20you%20apply%20it%20according%20to%20your%20work%20situation%3C%2FP%3E%3CP%3EGood%20Luck%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-776094%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776094%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20create%20template%20file%20with%20dummy%20data%20with%20all%20possible%20columns%20in%20your%20files%20and%2C%20if%20columns%20have%20names%20(texts%20in%20first%20row%20before%20data)%2C%20with%20exactly%20the%20same%20names%20as%20in%20your%20files.%20Combining%20the%20files%20take%20that%20template%20as%20the%20sample.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20one%20of%20the%20files%20compare%20to%20the%20template%20extra%20columns%20appear%2C%20or%20column(s)%20have%20another%20order%2C%20or%20column(s)%20have%20another%20names%20-%20such%20data%20will%20be%20missed%20or%20you'll%20have%20an%20error%2C%20depends%20on%20situation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20in%20the%20file%20there%20are%20no%20some%20columns%20as%20in%20template%2C%20data%20will%20be%20filled%20by%20null.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-777512%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-777512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20replies!%3C%2FP%3E%3CP%3ESo%20if%20make%20a%20template%20file%20with%20all%20the%20columns%20and%20headers%20that%20will%20be%20there%20in%20the%20end%2C%20it%20will%20add%20null%20data%20to%20columns%20where%20there%20is%20no%20data%20and%20add%20the%20data%20it%20has%20to%20the%20appropriate%20column%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20instance%20if%20I%20make%20a%20table%20template%20that%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECol%20A%3C%2FTD%3E%3CTD%3ECol%20B%3C%2FTD%3E%3CTD%3ECol%20C%3C%2FTD%3E%3CTD%3ECol%20D%3C%2FTD%3E%3CTD%3ECol%20E%3C%2FTD%3E%3CTD%3ECol%20F%3C%2FTD%3E%3CTD%3ECol%20G%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20my%20first%20worksheet%20looked%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECol%20A%3C%2FTD%3E%3CTD%3ECol%20B%3C%2FTD%3E%3CTD%3ECol%20D%3C%2FTD%3E%3CTD%3ECol%20E%3C%2FTD%3E%3CTD%3ECol%20F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20a%20second%20looked%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECol%20A%3C%2FTD%3E%3CTD%3ECol%20B%3C%2FTD%3E%3CTD%3ECol%20C%3C%2FTD%3E%3CTD%3ECol%20E%3C%2FTD%3E%3CTD%3ECol%20G%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20get%20an%20output%20like%20this%3F%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECol%20A%3C%2FTD%3E%3CTD%3ECol%20B%3C%2FTD%3E%3CTD%3ECol%20C%3C%2FTD%3E%3CTD%3ECol%20D%3C%2FTD%3E%3CTD%3ECol%20E%3C%2FTD%3E%3CTD%3ECol%20F%3C%2FTD%3E%3CTD%3ECol%20G%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3Babc%3C%2FTD%3E%3CTD%3E%26nbsp%3Babc%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3Babc%3C%2FTD%3E%3CTD%3E%26nbsp%3Babc%3C%2FTD%3E%3CTD%3E%26nbsp%3Babc%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eabc%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20so%2C%20would%20it%20matter%20if%20the%20columns%20weren't%20necessarily%20in%20the%20same%20order%20as%20long%20as%20they%20had%20the%20same%20headers%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBrian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778167%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Brian%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20practically.%20Initial%20result%20will%20be%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20855px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124854i582D602AE0B6F2AC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAfter%20filtering%20the%20template%20file%20and%20removing%20first%20column%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20364px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124858iC6C96C3A872FF365%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20as%20many%20files%20as%20needed%20into%20the%20folder%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778190%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20attached%20are%20template%20and%20two%20dummy%20files.%20Put%20them%20into%20the%20folder%20c%3A%5Ctest%20(only%20these%20files%2C%20nothing%20more)%2C%20file%20from%20previous%20post%20in%20any%20other%20place%20and%20be%20sure%20all%20together%20works.%20After%20that%20you%20may%20try%20to%20play%20with%20your%20real%20files%20in%20similar%20way.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779138%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EThanks%20for%20posting%20the%20files.%20Unfortunately%20I%20don't%20have%20access%20to%20my%20C%3A%2F%20drive...%20Will%20it%20work%20if%20I%20put%20it%20in%20my%20z%3A%2F%20drive%3F%20Or%20would%20the%20code%20need%20to%20be%20changed%3F%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBrian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-779518%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20multiple%20quarterly%20workbooks%20into%20one%20master%20workbook.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-779518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379238%22%20target%3D%22_blank%22%3E%40Brianbuchanan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBrian%2C%20I%20added%20the%20parameter%20to%20the%20attached%20file.%3C%2FP%3E%0A%3CP%3EWhen%20you%20start%20with%20Power%20Query%20most%20probably%20you'll%20see%20the%20warning%20what%20your%20version%20could%20be%20incompatible%20with%20queries%20-%20ignore%20it.%3C%2FP%3E%0A%3CP%3EOpen%20right%20pane%20with%20queries%20(Data-%26gt%3B%20double%20clink%20on%20Queries%20and%20Connections).%20It%20shall%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20281px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124954i3EEA8CAF4D5888E0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EDouble%20click%20on%20pFilePath%20and%20change%20it%20on%20yours.%20Now%20double%20click%20on%20Test%20and%20Refresh%20it.%20Before%20that%20ignore%20all%20errors.%3C%2FP%3E%0A%3CP%3EGroup%20of%20queries%20under%20Transform...%20is%20generated%20automatically%2C%20you%20query%20is%20only%20Test.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Brianbuchanan
New Contributor

Hi all,

I get quarterly excel files that I have been manually combining and I am wondering if there is a more efficient way that I can be doing it with less chance of user error?

 

Each quarter I get the following excel workbooks:

  1. An inpatient listing for my hospitals
  2. A listing of inpatients who had a complication
  3. A listing of inpatients who returned to the Emergency Department after discharge
  4. A listing of inpatients who were readmitted within 30 days of discharge.

Currently, each quarter I take the new data and append it to the bottom of a master excel workbook with a worksheet for each of the above books. This can be quite time consuming as occasionally columns are added into the quarterly reports so they don't line up with the columns of the master, and I have to adjust things.

 

Is there a way in excel that I can append the rows from each new workbook to its corresponding sheet in the master where the data will be placed in the column with the same header, and in situations where there is no existing matching header, insert a new column with that header to the master file? This would leave a bunch of blanks in the previous rows, but that wouldn't really be an issue for me.

 

Any guidance or suggestions would be greatly appreciated!

Cheers,

Brian

9 Replies

@Brianbuchanan 

 

Hi Brian

To my humble point of view, you have 2 options (depending upon the structure of your files)

Option 1:

Creating a Code in VBA

I am attaching an excel file with about 90 sheets and by triggering the code using the shortcut CTRL+ SHIFT + P , all the sheets are consolidated and appended, while the original sheets are deleted. (of course modifications can be done as needed, I am just showing you an example.

 

Option 2

Using Power Query to append any number of files together if they were put in the same folder. All what you need to do with every new file is to refresh the Query.

Here is a link to a similar situation explained by Excel MVP Mike Girvin

https://www.youtube.com/watch?v=_csX8sCzJd0

 

Hope that helps

Nabil Mourad

Download File: http://people.highline.edu/mgirvin/excelisfun.htm Goal: Unpivot and Clean Cross Tabulated Tables in multiple Excel Workbook Files and Create a Single Proper Data Set. Create a Power Query Function to repeatedly perform the Clean & Transform Task across many Excel Files. Lean how to

@nabilmourad,

Thanks for your answer! Macros seem to be blocked by my IT department, so I will see if I can make the power query method work - the tutorial you linked looks very helpful.

 

I have never used power query before - Will I be able to use it even if the columns are not consistent? As in, if there is a new column in the latest workbook, will it just create a new column in the file and have null values for the previous rows?

 

Cheers,

Brian

@Brianbuchanan 

Hi Brian

Although Power Query is doing magic, however, at the end it just repeats the same steps in the same sequence, assuming that you run the function to similar sources.

Also note that if you are recording among the steps, a step that keeps 3 columns and Deletes other columns >> so it will delete Other columns whether they are 5 or 6 or 7...

Once you get the concept, you apply it according to your work situation

Good Luck

Nabil Mourad

@Brianbuchanan 

You may create template file with dummy data with all possible columns in your files and, if columns have names (texts in first row before data), with exactly the same names as in your files. Combining the files take that template as the sample.

 

If in one of the files compare to the template extra columns appear, or column(s) have another order, or column(s) have another names - such data will be missed or you'll have an error, depends on situation.

 

if in the file there are no some columns as in template, data will be filled by null.

@Sergei Baklan and @nabilmourad,

Thanks for your replies!

So if make a template file with all the columns and headers that will be there in the end, it will add null data to columns where there is no data and add the data it has to the appropriate column?

 

For instance if I make a table template that looks like this:

Col ACol BCol CCol DCol ECol FCol G
       

 

And my first worksheet looked like this:

Col ACol BCol DCol ECol F
abcabcabcabcabc

 

And a second looked like this:

Col ACol BCol CCol ECol G
abcabcabcabcabc

 

I would get an output like this?

Col ACol BCol CCol DCol ECol FCol G
 abc abc  abc abc abc 
abcabcabc abc abc

 

And if so, would it matter if the columns weren't necessarily in the same order as long as they had the same headers?

 

Cheers,

Brian

@Brianbuchanan 

Hi Brian,

 

Yes, practically. Initial result will be as

image.png

After filtering the template file and removing first column

image.png

You may add as many files as needed into the folder

@Brianbuchanan 

And attached are template and two dummy files. Put them into the folder c:\test (only these files, nothing more), file from previous post in any other place and be sure all together works. After that you may try to play with your real files in similar way.

@Sergei Baklan,

Thanks for posting the files. Unfortunately I don't have access to my C:/ drive... Will it work if I put it in my z:/ drive? Or would the code need to be changed?

Cheers,

Brian

@Brianbuchanan 

Brian, I added the parameter to the attached file.

When you start with Power Query most probably you'll see the warning what your version could be incompatible with queries - ignore it.

Open right pane with queries (Data-> double clink on Queries and Connections). It shall be like

image.png

Double click on pFilePath and change it on yours. Now double click on Test and Refresh it. Before that ignore all errors.

Group of queries under Transform... is generated automatically, you query is only Test.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies