Refer to a simple 5 column representative inventory dataset of a Glass manufacturer:
From this inventory data, one has to furnish customer orders based on specific dimensions demanded by them. A typical Customer request would be to supply glass sheets as per the following dimensions
The firm may or may not have glass sheets of this specific size. The objective is to identify glass sheets, from the inventory on hand, which match customer specifications. If there is no exact match, then one must be able to obtain all inventory items which have the same Thk (MM) and CAT as the customer specified dimensions but the Length and thickness should be more than equal to the customer specified dimensions. The length and width can then be trimmed to match the exact customer dimensions. Furthermore, the result returned should:
- List only the Top 30 glass sheets available in inventory; and
- List those Top 30 glass sheets in ascending order of wastage (wastage caused when the glass sheet is trimmed to match the customer specified dimensions)
You may refer to my solution in this workbook. I have shared two solutions - one using Excel formulas and the other using Power Query a.k.a. Get and Transform in Excel 2016. Please read the Comments in cells F1, J9 and J16 of the "Solutions" worksheet. The difference between the 2 solutions is:
- Formula driven solution - This is in range J10:AM14 of the Solutions worksheet. This is a semi dynamic solution (as compared to the Power Query solution). To get the models in ascending order of wastage, one will have to create an Area column in the base data and sort that column in ascending order.
- Power Query solution - This is in range J17:AM21 of the Solutions worksheet. This is a dynamic solution. Just change the customer specified dimensions in range G2:J2 of the Data and Query worksheet. Thereafter just right click on any cell in the range below and select refresh.
Assume a single row of data with numbers and blanks appearing at random intervals. The objective is to sum the largest 5 of last 10 numbers in that row. Solving this problem entails multiple steps:
- Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers
- Identify the largest 5 of those 10 numbers
- Sum those largest 5 numbers
Here are the steps
- Suppose the numbers and blanks are in range A2:V2
- Type 10 in cell X1
- Enter this array formula (Ctrl+Shift+Enter) in cell X2
Assume there is data for complaints received and complaints resolved for two years - 2009 and 2010 for some regions regions. Each Region has its own worksheet. There are three headings on each sheet - Particulars, 2009 and 2010. While the Particulars column has the text "Complaints received" and "Complaints resolved", the year columns have number of complaints received and resolved.
The task is to create a Summary sheet which:
1. Show the consolidated figure of complaints received and complaints resolved for both years
2. Show the individual regions which make up the consolidated figure in 1 above.
In this workbook, you may see the data layout and four different ways of resolving the two tasks mentioned above.
You may also refer to the following related posts:
1. Summarise data from multiple worksheets with one condition
2. Summarise data from multiple worksheets with one condition - Part II
3. Summarise data from multiple worksheets
4. Summarise data from multiple worksheets with multiple conditions
5. Summarise data from multiple worksheets with multiple conditions - Part II
6. Summarise data from different cells of multiple worksheets
In a range of cells, assume there is a list of invoices raised during a certain period. Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range. So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507.
Given the following:
1. Starting and ending invoice number (two numbers) from the invoice book; and
2. List of actual invoice numbers raised during a certain period (as elaborated above)
one may want to generate a list of missing invoices.
You may refer to two solutions in this workbook.
In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched. Furthermore, the VLOOKUP() function returns data from one column only.
To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, you may refer to my solution in this workbook.
To work with a list where there are multiple occurrences of a particular string and data has to be extracted from one column only, you may refer to my solution at this link.
Data in a two column database (A3:B13) can be Auto filtered on column A with one or many conditions. Data in column B will be numbers only (positive, negative or 0's) - no text values.
After filtering data in column A, one may want to extract the unique numbers from column B to cell A18 (and then downwards). Data from cell A18 downwards should keep changing with different filter conditions specified in the Auto filter drop down of column A.
You may refer to my solution in the this workbook.
To extract unique values from an unfiltered range without any conditions, you may refer to the following link
To extract unique values from an unfiltered range with multiple conditions, you may refer to the following link
Assume data in range B4:B18 (heading is in cell B3). To compare the value in the current row with the value in the row below, one may enter the following formula in cell C4 and copy down
True's indicate matching values with the value in the next row. However, the above formula would fail when an auto filter is applied to the range.
To compare the value in the current row with the value in the next visible row below, refer to my solution in this workbook.
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 added
2. Sheets deleted
3. Sheets renamed
4. Sheets repositioned
While this can be accomplished by using VBA, you may refer to my formula based solution here.
To generate a list of all Excel files in a specific folder, you may refer to the following post.
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 are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table).
You may watch a short video of my solution here
Solution for all versions of MS Excel
You may refer to my array formula solution.
You may refer to both solutions in this workbook.
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 are very clearly mentioned in the this workbook.
You may also refer to the a similar article at the following link.