Tags: SMALL

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{ 5 Comments }

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer: Model Length (MM) Wide (MM) Thk (MM) CAT HX9-G-ARD 1071 273 3.5 A MYP-G-3RD 580 535 3.2 B EPO-G-3RD 580 535 3.2 A MYG-G-3R 966 350 3.2 A MYN-G-3RD 649 530 3.2 A GM SPIN-G-3FD 882 395 3.2 A MY8-G-AR 880 400 3.5 B […]

Read More →

Sum the largest 5 of the last 10 numbers in a row ignoring blanks

{ 2 Comments }

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

Read More →

Show granular as well as total figures on the Summary sheet

{ 0 Comments }

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

Read More →

Generate a list of missing invoice numbers

{ 2 Comments }

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

Read More →

LOOKUP unique data from multiple columns where search string appears multiple times

{ 2 Comments }

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

Read More →

Dynamically extract unique values from a filtered range

{ 0 Comments }

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

Read More →

Compare value of one cell with value of next visible cell of a filtered range

{ 2 Comments }

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 =B4=B5 True’s indicate matching values with the value in the next row.  However, the above formula would fail when […]

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 →

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 →

Programmatically transfer data from master sheet to sub sheets with conditions

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