Input: Multiple Excel files with a single sheet of data.

Download sample input file here.

Download all sample workbooks here.

Goal: Setup a DataFromExcel formula that collates the data from all the workbooks and puts it in one single place.

Hint:

1. Get the file list using GetAllFiles

2. Setup the import via the UI for DataFromExcel on a single file.

3. Modify the formula to link to all files via RegionDown.SK

Input: A single Excel file with multiple sheets of data. Each sheet has the same columns of data, but the number of sheets is not known in advance.

Download sample input file here.

Goal: Setup a DataFromExcel formula that collates the data from all the sheets and puts it in one single place.

Input: Multiple Excel files with multiple sheets of data.

Download sample files here.

Goal: Setup a DataFromExcel formula that collates the data from all the workbooks and worksheets and puts it in one single place.

Hint:

1. Get the file list using GetAllFiles.SK

2. Define a function which will take in a file_name as input and return a two column list of the file_name & the sheet_name. (Use SheetNames.SK)

3. Use the above defined function on the list of files

4. Use DataFromExcel.SK, where you can pass in the filename & sheetname via RegionDown.SK to get the data in one single place.

Input: Multiple Excel files with a single sheet of data. The sheet name keeps changing every time you get the data file. In such a scenario, you can use the sheet number instead of the sheet name to pull the data from the file.

Download sample input files here.

Goal: Setup a DataFromExcel formula that pulls the data from the first sheet of a given file (immaterial of the sheet name) and puts it in one single place.

Hint: Use the number 1, instead of the sheet_name in the DataFromExcel.SK formula.

Input: An Excel file having multiple columns with repeated headers.

Download sample input file here.

Goal: Setup a DataFromExcel formula that pulls the data from all the columns even when column headers have been repeated and puts it in one single place.

Hint: In the options area written by the UI of DataFromExcel.SK, change the header to header~1/3 OR header~2/3. The syntax can be read as follows: header~1/3 represents "first header among 3 such occurrences".

Input: Multiple Excel files with data starting at different positions in the top 20 rows. The data is known to be in the first few columns but the row where the data starts is uncertain.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from all the files in the given sample set (immaterial of where the data starts) and puts it in one single place.

Hint:

1. Define a function which will return the row where the data starts given a file.

2. Use GetAllFiles.SK to get the set of input files.

3. Run the function on each file to get the corresponding data start row.

4. Use string concatenation in Excel to define the range as "RegionDown.SK(A"&datastartrow&":G"&datastartrow&")"

5. Use the range for each file as an input into DataFromExcel.SK to get the data from the respective files.

Input: Excel file with blank rows in data.

Download sample input file here.

Goal: Setup a workbook formula that pulls the data from the sample file.

Hint:

1. Use the UI DataFromExcel.SK to select the first range of data in the input file.

2. Select the option to Discard Blank Rows in Selection in the UI.

Input: Excel files with blank or redundant data columns.

Download sample input file here.

Download file with redundant data columns here.

Goal: Setup a workbook formula that pulls the data from the sample file as well as the additional data file with redundant data columns.

Hint:

1. Use the UI DataFromExcel.SK to setup the import from the sample input file.

2. Change the "RegionDown.SK(A1:G1)" in the formula to "RegionDown.SK(A1:AA1)"

3. Run the file on the second file with extra columns to extract the desired result.

Input: Excel files with fixed number of columns but variable column headers.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from all the sample files and puts it at one place.

Hint:

1. Use the UI DataFromExcel.SK to setup the import from the sample files by using Column Numbers instead of Column Headers.

Input: Excel files with fixed number of columns but variable column headers.

Download sample input files here.

Goal: Setup a workbook formula that pulls the data from all the sample files and puts it at one place.

Hint:

1. Use the UI DataFromExcel.SK to setup the import from the sample files.

2. Within the space where options are written onto the Excel workbook, make changes on the row showing If Blank. (Default value is #N/A)

Notes:

1. Notice that some values will be empty strings (which seem like blanks in the imported data).

Input: Excel files with possibly missing columns of data.

Download sample input files here Sample files

Goal: Setup a workbook formula that pulls the data from all the sample files and puts it at one place. Further, the workbook should give either errors or warning based on which columns are missing.

Hint:

1. Use the UI DataFromExcel.SK to setup the import from the sample files.

2. Within the space where options are written onto the Excel workbook, make changes on the row showing If Blank. (Default value is #N/A)

Notes:

1. Notice that some values will be empty strings (which seem like blanks in the imported data).