Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook. The task is the compute the MODE of these numbers. Mode is defined as the value which appears most frequently in a range of cells. So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in […]
Read More →
{ 72 Comments }
Assume there is data in range A2:BG2185 of a worksheet (Range A1:O1 have headings). In column A are years and in column B are Countries. Other columns have some text/numeric data. In column A are years ranging from 1984 to 2009 for each country mentioned in column B. Therefore, Albania would appear in range B2:B27 and 1984-2009 […]
Read More →
{ 74 Comments }
In this file, the source data sheet is a pasted special pivot table. The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet. Effectively, we need to convert a matrix like data layout to a tabular layout. We need to “denormalise” the pivot table. Solution for […]
Read More →
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 →
{ 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 →
{ 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 →
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 →
{ 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 →
Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
Compute MODE of all numbers split across multiple worksheets
{ 10 Comments }