Tags: SMALL

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 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

Generate a list of missing invoice numbers

{0 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 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.

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, 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.

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 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

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

{0 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 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.

Generate a list of all tabs names without using VBA

{93 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 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.

Dynamically transpose data after ignoring blank cells

{0 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 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.

Programmatically transfer data from master sheet to sub sheets with conditions

{22 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 are very clearly mentioned in the this workbook.

You may also refer to the a similar article at the following link.

Blanks appearing in source data not to appear in Data validation list

{0 Comments}

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 to show only the entries in the validation drop down (without the blanks).  The question and instructions are very clearly mentioned in this workbook.

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

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

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

Determine weekday after factoring in user specified holidays

{0 Comments}

Given a certain beginning date and specified number of days, one may want to determine the ending date (beginning date plus number of days).  When determining the ending date, the following two cases should be considered

Case 1

Holidays are:

1. Sundays only; and

2. User specified public holidays

Case 2

Holidays user specified public holidays only.  Saturdays and Sundays are working days

If the landing date is any of the two holidays, the result should be the next working day.

You may refer to my solution in this workbook.