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 →
{ 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 →
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 →
Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range. Furthermore, one may want to provide an additional date criteria as well. You may refer to my solution in this workbook.
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 →
{ 2 Comments }
Assume there are three worksheets, one each for 2008, 2009 and 2010. On each sheet there are two columns for Account code and Account description. Thereafter there are 12 columns (one for each month). Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]
Read More →
{ 0 Comments }
The objective is to determine the quantity of stock (of a particular type) to be transferred from “Stock surplus” locations to “Stock deficient” ones. If all the requirements cannot be met from one location, tap other locations. The final output should show the location from where stock is being transferred. Furthermore the order of determining […]
Read More →
{ 8 Comments }
Assume there are two columns with data repeated in all both columns. One may wan to extract the unique text values from a specific column based on conditions specified by end-user. Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem Solution for MS Excel 2010 and higher versions […]
Read More →
Extract n th. most frequently occurring item from a database
{ 2 Comments }