Tags: MOREFUNC

Extract data from multiple cells of closed Excel files

{219 Comments}

Assume a folder named Test on the desktop.  In this Test folder, there is another folder named Survey reports.  In the Survey report folder, there are multiple Excel files.  All the Excel files have the same structure i.e. if there is an Operator Names in cell C9 of one Excel file, then in other Excel files as well, there will be the operator name in cell C9 itself.

In another workbook (say Book1.xlsx), one may want to extract data from specific cells of all Excel files lying in the Survey report folder.  Therefore, in sheet1 of Book1.xlsx, one may want to show in A3:C3 data from cells C4, R9 and cell C16 of the first Excel file from the Survey report folder.  Likewise, in sheet1 of Book1.xlsx, one may want to show in A4:C4 data from cell C4, R9 and cell C16 of the second Excel file from the Survey report folder and so on.

While one way is to open each file manually and then link individual cells, this is a very time-consuming process if there are many files in the Survey reports folder.

You may follow the process mentioned below to extract data from specific cells of multiple closed Excel files.

1. All files in the Survey reports folder should be closed
2. In sheet1 of Book1.xlsx, type C4, R9 and C16 in A1:C1
3. From cell D3 downwards, type names of all Excel files (along with their extension i.e. xls, xlsx) in the Survey report folder.  To generate this list of Excel files names automatically, refer to the following post.
4. Download and install the Morefunc addin from here.  This addin will allow us to use the INDIRECT.EXT function.  For MS Excel 2007 and prior versions, one can simply download, install and use this addin.  In Excel 2007, once this addin is installed, it will appear under Formulas > Morefunc.  For Excel 2010, the process for installing this addin is as follows:

a. Unzip the downloaded folder and double-click on the Setup file
b. Navigate to the following folder C:\Program Files\Morefunc and copy three files - Morefunc, Morefunc11 and Morefunc12
c. Navigate to the following folder C:\Program Files\Microsoft Office\Office14\Library and paste the files there
d. Open MS Excel 2010
e. Go to Files > Options > Addins > Manage Excel Add-ins > Go
f. Check the following three boxes - Morefunc (add-in functions), Morefunc Tools and Morefunc12
g. Click on OK
h. Close MS Excel 2010 and reopen
i. Morefunc should now appear under the Formulas Tab in the Ribbon

5. In cell A3 of sheet1 of Book1.xlsx, enter the following formula and copy till C3 and downwards

=INDIRECT.EXT("'C:\Users\Ashish\Desktop\test\Survey Reports\["&$D3&"]Sheet1'!"&A$1)

You will observe that even with all Excel files in the Survey reports folder closed, the INDIRECT.EXT function will display the data from respective cells of those Excel files.

Since data is being extracted from closed Excel files via an addin, performance of the workbook will take a hit.  Please try this on limited Excel files first.

Remove special characters and numbers from an alphanumeric string

{4 Comments}

Hi,

Assume the following alphanumeric strings in range A3:A6

A-2222 Mohan 12-4 #$% Singh$%&***
**** Sanchit #$% Pal$%&***
$^%$^$ Sudhir #$% Si$$$ngh$%&***
#@234 Krishan #$% Oman$%&***

In range B3:B6, one may want to extract only the text values i.e. drop special characters and numbers from the values in range A3:A6.  In range B3:B6, the result should be:

A Mohan Singh
Sanchit Pal
Sudhir Singh
Krishan Oman

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.

You may refer to my solution in this workbook.

To remove only special characters appearing at the beginning and end, you may refer to my solution at the following link.

Display auto filter criteria in a cell

{2 Comments}

Assume data in range D6:E11 - months in D6:D11 and numbers in E6:E11.  Headings are in D5:E5.  On filtering multiple values in column D, one may want to view the filtered criteria in cell (separate criteria by comma for multiple selections in auto filter drop down).

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.  This add in will enable us to use the following two functions

1. UNIQUEVALUES(); and
2. MCONCAT()

You may refer to my solution in this workbook.

Determine stock transfer from multiple locations

{0 Comments}

The objective is to determine the quantity of stock (of a particular type) to be transferred from "Stock surplus" locations to "Stock deficient" ones.  If all the requirements cannot be met from one location, tap other locations.  The final output should show the location from where stock is being transferred.  Furthermore the order of determining "Stock transfer location" is descending order of stock available in "Stock surplus" locations.  Stock available in "Stock surplus" locations should be determined after setting off quantity already transferred to other locations.

For solving this problem, you will have to download and install the morefunc addin from here.  For instructions of installing the addin for Excel 2010, please refer point 4 of the following post.

You may refer to my solution in this workbook.