Merging across excel workbooks for Mac

[asha_]

Hi! I need to merge across 100+ excel workbooks in excel 2021 for mac. Each has only 1 sheet worth of data with identical row labels. However, there are additional issues: (1) data missing where cells were left blank across the 1-15 some odd rows of data within each workbook/sheet. I'd like these replaced with -999 or something like that to indicate missing data. (2) columns for dates needs to have a new variable/row added that transforms them into a desired format. I.e., change April 16, 2002, Wednesday to just 4/16/02. and (3) there are two columns/ separate variables that have alerts indicating number stored as text. These all need to be changed to just number with no decimal places. Finally files will need to be sorted chronologically by date after date has been transformed. I am interested in using

Ablebits Ultimate Suite for Excel or some code/macro. I do not wish to make each of these edits by hand/drop down menu nor do I wish to do a cut and paste into a master file given the likelihood of human error!

Thank you!!!

 

[Jim Gordon]

Hi Asha

Are you familiar with programming languages, Visual Basic For Applications, in particular? The task you wish to perform might be faster manually if you're not already familiar with programming. For help with working with files, I recommend Ron DeBruin's pages.

You will have to figure out the logic of the operations you wish to perform to cover all the various permutations you might encounter.

I am sure all of this can be done programmatically, but with such a small number of workbooks involved, I think the time it takes to write the code would exceed the time it would take to perform the tasks manually. If you had 1,000 workbooks then maybe it would be worth taking the time to learn how to program this operation.