Author: Ashish Mathur

Generate a list of all Excel files from a specific folder without using VBA

{ 56 Comments }

Assume a folder named Test on the desktop.  In this Test folder, there is another folder named Survey reports.  In the Survey reports folder, there are Word documents, PowerPoint files, Excel files, PDF files and all sort of other files types. In a new workbook, one may want to list down all Excel files from […]

Read More →

Display text entries in the data area of a pivot table

{ 60 Comments }

Assume a two column database of approximately 20,000 rows (say from A1:B20237).  The first column has vendor names and the second column has Part codes (alphanumeric string).  Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1.  One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]

Read More →

Determine the maximum number of consecutive 1’s appearing in a range

{ 26 Comments }

Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1’s and 0’s.  A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”.  Similar data is entered for other customers in B7:M500. […]

Read More →

Shade alternate band of rows in a filtered range

{ 4 Comments }

Assume the following numbers in range C5:C22.  Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]

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 →

Calculate turn around time excluding Sundays and public holidays

{ 206 Comments }

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays. You may refer to my solution in this […]

Read More →

Extract n th. most frequently occurring item from a database

{ 2 Comments }

Assume a database in range D5:D11 with the following values – Black, Black, Purple, Purple, Grey, Grey, Grey.  To extract the most frequently occurring item, one may use the following formula =INDEX($D$5:$D$11,MODE(MATCH($D$5:$D$11,$D$5:$D$11,0))) To extract the second most frequently occurring item, try the following: 1. Enter 2 in cell C16: 2. In cell D16, enter the […]

Read More →

Summarise data from multiple sheets with one condition – PartII

{ 2 Comments }

Assume a worksheet which has scores (1-10) on four future dates on nine questions.  Each row represents responses for one question.  Likewise there are 20 respondents i.e. 20 worksheets which have the same structure. One may want to summarise data from all 20 worksheets into one worksheet.  The question and solution are more clearly explained in the attached […]

Read More →

Generate a list of all tabs names without using VBA

{ 252 Comments }

Assume an MS Excel file has 4 worksheets – Sheet1, Sheet2, Sheet3 and Sheet4.  Insert a sheet before Sheet1 and name that tab as Summary.  On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards.  Furthermore, the sheet names so generated, should be dynamic for the following changes: 1. Sheets […]

Read More →

Extract farthest/latest date based on multiple conditions

{ 0 Comments }

Assume a three column database showing Site ID, Customer, Status and Requested Date.  On the same site ID, the same customer may have different status on different dates.   In such a scenario, one may want to know the farthest/latest requested date and its corresponding status for all unique combinations of Site ID and Customer. I initially attempted to […]

Read More →