Tags: OFFSET

Summarise data from multiple sheets with multiple conditions – Part II

{64 Comments}

Assume date wise revenue data has been shown on two sheets - Sports wear and Fitness equipment (Data worksheets).  On each of these two worksheets, there are two columns - Date and Revenue.  On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions.  The conditions for summarising are:

1. User specifies upper and lower revenue figures - Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
2. User specifies upper and lower date limits - Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
3. User specifies upper and lower revenue figures AND User specifies upper and lower date limits - Revenue figures from both sheets lying between these figures and dates should get added as well as rows should get counted (to know instances)

The restrictions are as follows:

1. Do not want to consolidate data from two data worksheets into a single worksheet
2. Do not want to perform any ancillary calculations on the two data worksheets

If these conditions are relaxed, then alternative solution such as Pivot Tables and SUMPRODUCT() function can be used.

You may refer to my solution in this workbook.

I have solved a related problem of summarising data from multiple worksheets here.

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

Valuing Closing Stock using FIFO method of Accounting

{12 Comments}

Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices.  On another sheet, the closing stock of each Raw material type is entered.  In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued is from the oldest stock lying in inventory i.e. the inventory left over will be from the recent stock purchased.

The objective is to determine the value of the closing stock of Raw material on FIFO basis.

You may refer to my solution in the attached workbook.

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.

Shade alternate band of rows in a filtered range

{0 Comments}

Assume the following numbers in range C5:C22.  Heading is in cell C4.

Range C5:C7 - 11
Range C8:C11 - 14
Range C12:C13 - 23
Range C14-C19 - 56
Range C20-C22 - 78

One may want to colour cells for every change in number in range C5:C22 with the following two modifications:

1. Shading should be for alternate band of rows i.e. C5:C7 should be coloured, C8:C11 should not be coloured, C12:C13 should be coloured, C14:C19 should not be coloured and C20:C22 should be coloured.

2. Shading should vary with the range being filtered i.e. If range C5:C22 is filtered to exclude 14 and 56, then numbers 11 and 78 should be coloured i.e. range C5:C7 and C20:C22 should be coloured.

To accomplish this, try the following:

a. Select cell C5 and go to Home > Format > Conditional formatting > New Rule > Use a formula to determine which cells to format

b. In the formula box there, enter the following formula

=AND(ISODD(COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(C$5,ROW(C$5:C5)-ROW(C$5),)),MATCH(C$5:C5,C$5:C5,0)),ROW(C$5:C5)-ROW(C$5)))),COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(C$5,ROW($C$5:$C$22)-ROW(C$5),)),MATCH($C$5:$C$22,$C$5:$C$22,0)),ROW($C$5:$C$22)-ROW(C$5)))>1)

c. Click on Format and select any Fill colour

d. Click on OK > OK > Apply

e. Copy cell C5, select range C6:C22 and right-click > Paste Special > Formats

With no filter in place, colouring will happen as mentioned in point 1 above.  Auto filter the range now on some values and the colour banding should change.

Sum visible cells of a filtered range ignoring errors

{2 Comments}

In a multi column database, assume a filter has been applied on some columns.  Post filtering, some error values (such as #DIV/0!, #NA etc.) appear in the one of the numeric columns.   The objective is to sum numbers in a numeric column.  The usual SUM() function would not work because the range of sum function should be error free.  Furthmore, the SUM() function would also include the invisible rows of a filtered range.  The SUBTOTAL(9,range) function would not work either because the range in the SUBOTAL() function should also be error free.

Assume that the database is in range A11:B20.  Column A has been filtered and column B is the column of numbers which also has the error values.

For Excel 2007 and prior versions, the following array formula (confirmed by Ctrl+Shift+Enter) will sum the visible rows a filtered range ignoring error values.

=SUM(IF(NOT(ISERROR(SUBTOTAL(109,OFFSET(B10,ROW(B11:B20)-ROW(B10),)))),SUBTOTAL(109,OFFSET(B10,ROW(B11:B20)-ROW(B10),))))

For Excel 2010 and higher versions, the following formula will work

=AGGREGATE(9,7,B11:B20)

Average a range of numbers with blanks appearing at random intervals

{2 Comments}

With blanks appearing in a range of numbers, one may want to:

1. Average the first n numbers; and/or
2. Sum the last n numbers (in a horizontal and vertical range)

Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached.

You may refer to my solution in this workbook.

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)

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.

Updating charts for columns added to source data in Excel 2003

{0 Comments}

Having created a graph from a certain source data, if columns get added later to the source, they do not automatically append to the graph.  While this is easy enough to accomplish in Excel 2007 and higher versions, doing the same in Excel 2003 is a different procedure altogether.  Instructions are very clearly mentioned in this workbook.