Category: DATA EXTRACTION

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 →

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 →

Dynamically transpose data after ignoring blank cells

{ 2 Comments }

In range A7:A15, assume the following values A, S , empty cell, empty cell, R, A, W, A, SD.  Starting from cell B17, transpose data to the right (ignoring the blank cells).  Therefore, in range B17:F17, the result should be A, S, R, W SD. Solution for MS Excel 2010 and higher versions If you […]

Read More →

Extract number from an alphanumeric string

{ 72 Comments }

Given an alphanumeric string, one may want to perform the following Extract phone number from the string Assume a list of customer addresses with multiple phones numbers mentioned in the address field itself.  These numbers may be mobile numbers and/or mobile numbers.  Furthermore, PIN codes may also be mentioned in the address string. One may want […]

Read More →

Programmatically transfer data from master sheet to sub sheets with conditions

{ 43 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 →

Create a master list of unique account codes from two data sources

{ 2 Comments }

Assume a two column database in range C4:D11.  In cell C3, type “Cheque No.” (without double quotes) and in cell D3, type “As per Bank Account” (without double quotes).  Entries in range C4:C11 are cheque numbers and in range D4:D11 are Dollar values.  Let’s refer to this database as “The Bank Dump”. Assume another two column […]

Read More →

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

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 →

Performing a text to rows operation

{ 19 Comments }

Given multiple text values in a cell which are separated by a semi-colon, one may want to split data into multiple rows along with existing data in other columns.  This is something similar to Data > Text to columns – the only difference being that data has to be split by rows instead of columns. […]

Read More →