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 →
{ 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 →
{ 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 →
{ 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 →
{ 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 →
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 →
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 →
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 →
{ 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 →
{ 20 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 →
Generate a list of all Excel files from a specific folder without using VBA
{ 56 Comments }