Tags: MACRO

Filtering a database by both rows and columns

{34 Comments}

In range A1:BG2185 of this workbook, there is macro economic and demographic data for years from 1984 to 2009 (26 years) for 84 countries .  Furthermore, there are 57 parameters being tracked for each of the 26 years for all 84 countries.

There are missing observations for some years across many parameters and many countries.  If any country has more than 11 missing observations in any one parameter, then they should be depicted in the sheet named "Missing Observations".  The "Missing Observations" sheet should show three columns:

1. Country Name
2. Parameter with more than 11 missing observations
3. Observations missing

Compare two sheets and prepare a Discrepancy Report

{4 Comments}

In this workbook, the worksheet named Bookings shows flight booking data as per the company's records.  The Worksheet named Vendor data shows bookings as per the vendor's records.  There are discrepancies at three levels between these two sheets.

1. There could be PNR's in the company records (column B of Bookings) which are not present in the vendor's records (column C of Vendor Data)
2. There could be PNR's in the vendor's records (column C of Vendor Data) which are not present in the company records (column B of Bookings)
3. For PNR's that match between two sheets, the fares could be different (column K of bookings sheet and column L of Vendor sheet)

The objective is to:

1. Create a sheet named PNR discrepancy which lists down points 1 and 2 mentioned above
2. Create a sheet named Fare Mismatches which lists down PNR wise fare mismatches between two sheets - point 3 above.

Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook

{8 Comments}

Assume there are multiple files saved in a specific folder.  There can be Excel, Word, PDF, PowerPoint and other files types saved in this folder.  Furthermore, Excel files can have following extensions - .xls,.xlsx.xlsm.  Each Excel file has multiple worksheets with one worksheet being named Sheet1.  While data on sheet1 of all Excel files will start from cell B3 (first header cell), there can be a variable number of rows and columns from cell B3 onwards.

The task is to consolidate data from sheet1 of all Excel files to multiple worksheets of one Excel file.

The Question and Instructions have been clearly explained in the "Question and Instructions" worksheets of this file.

To see the solution work,

1. Save the following zipped folder on our desktop.  Unzip the folder and place Data Files folder (with all its files) on your desktop; and
2. Change the path in cell B2 of worksheet "Query and file listing" of Destination.xlsx file to the path of your folder saved in step 1 above.
3. Click on the blue button on worksheet "Query and file listing" of Destination.xlsx file.

Split data from a master document into various worksheets based on a template sheet

{19 Comments}

Assume a base data sheet with information for various vendors.  There is also a worksheets which has the template of the reconciliation statement which is sent to all vendors.  At the click of a button, one may want vendor worksheets to be created from the template.

Furthermore, one may want to do either of the following:

1. Create sheets for all vendors at once;

2. Create individual sheets selected by the end user

You may refer to my solution in this workbook.

Transfer data from one Excel file to multiple Excel files

{18 Comments}

Assume a two column database on a sheet named Demerge.  There are duplicate values appearing in column A of this database.  One may want to transfer all records of each unique entry in column A to as many different workbooks are there are unique entries in column A.  So if there are six unique values in column A, then six workbooks should get created (with two columns) and these workbooks should get stored on a specific folder in the desktop.

You may refer to my solution in this workbook.

Removing dependent validation list from cell for one case

{4 Comments}

In cell B6, a user can select one of the following entries from a validation drop down list - Reason1, Reason2 and Reason3.  If the user selects either Reason1 or Reason2, a dependent validation drop down lists should appear in cell C6.  However, if the user select Reason3 in cell B6, the dependent validation list from cell C6 should vanish thereby allowing the user to enter any value in cell C6.

You may refer to my solution in the this workbook.

Consolidate data from multiple closed Excel files with different number of columns

{6 Comments}

Assume a folder on the desktop named Test.  Within this folder there is a another folder named Monthly Data.  Within this folder are MS Excel files received every month.  The number of columns in each file is not the same.  Therefore in the January .xlsx file, there would be 100 columns, in the February.xlsx file, there could be 115 columns and so on.  The number of columns in every month's file will always be greater than the number of columns in the previous month's file i.e. If at all the number of columns across files are different, there will only be additional columns, not fewer columns.  Therefore for calendar year 2012, if all files have been received for January to May 2012, the columns in May.xlsx file will always include the columns in other four MS Excel files.  One may very well assume the following:

1. Spelling of headings across all files are the same; and
2. Headings are always in row 1 across all files (from cell A1); and
3. Worksheet tab in which information is present in all files in named "Sheet1"; and
4. In the monthly data folder, there are only those Excel files from where information needs to be consolidated; and
5. The files could be saved in any Excel format i.e. .xls, .xlsx, .xlsm; and
6. The number of rows in each file is variable

In a Summary workbook, one may want to consolidate information from sheet1 of all workbooks lying in the Monthly Data folder.  Given different number of columns in each file, even if one had the patience to copy and paste information, it will not be possible to do so because of different structures.

You may try the following steps to consolidate information:

1. Open the most recent file, say May.xlsx, and copy the headings from row 1 to this new file (paste in cell A1)
2. Press Ctrl+F3 > New.  In the Name box, type FL.  In the Refers to box, type the following:

=FILES("C:\Users\Ashish\Desktop\Test\Monthly data\*.xls")

3. In cell EE2, enter =IFERROR(INDEX(FL,ROW()-1),"") and copy down till blanks start appearing.  This step will list down all file names from the Monthly Data folder
4. Save the attached Excel file on your desktop, press Alt+F8 > Macro1 > Run

You may refer to my solution in the this workbook.

Create charts on different sheets by clicking a button

{6 Comments}

Assume a five column database with the first column containing codes, second one containing a separator, third one containing some alpha values, fourth one is a combination of the first three columns and the last column has response times.

One may want to create as many charts as there are codes in column A.  The charts should get created on different sheets with the click of a button.

You may refer to my solution in this workbook.

Programmatically transfer data from master sheet to sub sheets with conditions

{35 Comments}

A user inputs raw material purchase data in a master sheet.  This data is entered by date and vendor.  One may want to view individual vendor sheets at a monthly level.  In the attached file, i have presented two solutions - a macro based one and a formula based one.

The data layout and instructions are very clearly mentioned in the this workbook.

You may also refer to the a similar article at the following link.