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.  […]

Read More →

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 […]

Read More →

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 […]

Read More →

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

{ 22 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 […]

Read More →

Transfer data from one Excel file to multiple Excel files

{ 22 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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

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 […]

Read More →

Programmatically transfer data from master sheet to sub sheets with conditions

{ 45 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 […]

Read More →

Extract uncoloured cells per article to another worksheet

{ 0 Comments }

Given a database where only some waybill numbers per client are coloured,  one may want to query the database to fetch to another worksheet only those waybill numbers which are uncoloured. You may refer to my solution in this workbook.

Read More →