Redimensioning 18

Readers who have read some of my earlier posts will be familiar with the concept of “redimensioning” an array.

This is an extremely useful and important technique, which, in its basic form, allows us to take a two-dimensional array and convert it into one of just a single dimension, whilst of course retaining the elements within that array.

Such an approach is necessary if we wish to further manipulate the entries of some two-dimensional array. For example, we might be in a position in which, for whatever reason, we need to pass each of the entries in a two-dimensional array to an array of one or more parameters for further processing. However, since the evaluation of the resulting multi-dimensional “matrix” is not within Excel’s capabilities, we are obliged to first transform the original array to one of a single dimension.

More…

Which numbers add up to total? (2): Multiple Solutions 12

Note to readers: this post has been updated due to the inclusion – at the request of Torstein – of a further version of this solution, in which the number of values to be considered is dynamic and so may be set by the user. This version may be found at the very end of this post.

This post, inspired by a question from Patrick MacKay, from Belgium – thanks, Patrick! 🙂 – is a (rather belated) follow-up to that which I made here, in which, to recap, I presented a formula-based set-up which, given a target figure plus a series of values, determined which, if any, combination of those values had a sum equal to the target.

The only slight drawback to that solution was the caveat that, if more than one combination of values existed which satisfied that condition, then only one of those combinations was given.

Here I would like to improve upon that set-up by presenting a refined version which will return all such combinations. What’s more, at the very end of this deconstruction I will give a further version of the solution in which the number of values to be considered is a variable which may be set by the user.

In fact, that early post was also one of the very few in which I did not give an explanation as to how the solution works, which I would like to do here.

As an example of the output, imagine that our target value – £1054.35, for example – is here in A1, and that we have a list of 10 values in A2:A11, as below:

Which Numbers Add Up To Total (Multiple Solutions)

More…

Sorting a List Alphabetically (Without Filters) 17

In this post I would like to present a formula-based solution which returns an alphabetically-sorted list of the entries from a given range. Effectively, then, the formula gives equivalent results to those obtained using the in-built sort feature (though which, for whatever reasons, we may not be in a position to use).

For example, given the unsorted list in A2:A11 as below, we will return the ordered results as given in B2:B11.

Sorting a List Alphabetically (Without Filters)

More…

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range 9

I recently received a request from James, who was interested in a formula-based solution to the following problem: given a two-dimensional range containing a mixture of numbers and empty cells (which I am defining as being either “genuinely” empty or as containing the null string “” as a result of formulas in those cells), generate a unique list of those numbers in order of their frequency within that range, with the most frequent first. What’s more, if two or more numbers occur the same number of times within that range, then they should be listed in order of their size from smallest to largest.

For example, for the dataset in A1:F6 below, we would return the list as given beginning in I1.

Unique, Ordered List of Most Frequent Numbers in a Two-Dimensional Range

More…

Unique, Alphabetical List from Several Columns 25

In this post I shall present a method for generating a unique, alphabetical list in a single column from data contained within a contiguous range comprising several columns.

For example, given the dataset below in A2:E5, we will return that list beginning in cell G1:

Unique, Alphabetical List from Several Columns

More…

Collating from multiple sheets based on conditions 13

Some of us may be familiar with the standard technique using INDEX, SMALL, etc. which, given a single-column or single-row array, we can use to return a list of only those values which satisfy one or more criteria of our choosing.

In a previous post (see here) I outlined a method which, given a range consisting of more than one column, returned a single column consisting of all non-blank entries from that range. It can easily be verified that the single condition within this formula (i.e. that the entry be non-blank) can be extended to multiple criteria and so, effectively, we now have at our disposable the means with which to generate single-column lists from both one- and two-dimensional arrays.

But can we go one further yet again? “Three-dimensional” is the collective term often applied to those formulas in Excel which are capable of operating over not just single columns or rows, nor yet ranges consisting of multiple columns or rows (two-dimensional), but which also function effectively over multiple worksheets.

More…

Advanced Formula Challenge #6: Results and Discussion Reply

Last week I set readers the challenge which can be found here.

This one was perhaps a little less complex than ones I’d set in previous weeks, though of course it would still, in my opinion, fall within the boundaries of what I would deem “advanced Excel”.

It also demonstrates some techniques which we can apply to solving problems involving non-contiguous ranges, and in particular tell us which functions may be applicable to such set-ups.

Two good solutions received from John Jairo V and cyrilbrd (and Bill‘s was practically there as well, but for a small amendment – and the fact that I didn’t structure the question in full to begin with – sorry!).

More…

List of unique entries from column of space-separated strings 5

Given the list below in A1:A10, we may wish to create a list of unique, single words from that list, as per column B here.

Unique From Space Separated

We can do this with the following set-up: More…

Advanced Formula Challenge #4: Results and Discussion Reply

Last week I set readers the challenge which can be found here.

This one turns out to be a good deal more complex than it at first appears, and so perhaps not surprisingly no correct results were received..

GreasySpot at first thought that Advanced Filter would be a viable solution, but quickly realised that it wasn’t actually appropriate here. Besides, as I mentioned, the idea of this (and of all these challenges in fact) is to try to achieve the results using worksheet formulas alone.

So how can we achieve our desired results?

More…

Advanced Formula Challenge #3: Results and Discussion 7

Last week I posed readers the challenge which can be found here.

One solution was received, again from Bill, and this time it was not only correct, but a very good solution indeed. So congratulations again to Bill!

In fact, rather than dissect my own solution this week (which in any case differs only in minor details from Bill’s), I would like to present a breakdown of the solution given by Bill, as follows:

More…

ROW vs ROWS for consecutive integer generation 6

Often we wish to incorporate into our formula a construction which, as that formula is copied down to successive rows, will generate a series of consecutive integers, usually beginning with 1.

A classic example is the standard INDEX/SMALL set-up for returning multiple values corresponding to a certain set of criteria, e.g.:

=INDEX($B1:$B10,SMALL(IF($A$1:$A$10="A",ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),1))

More…

Advanced Formula Challenge #1: Results and Discussion 4

Last Sunday I set a challenge to readers to come up with a solution to the problem here.

Even though this site’s only been up for one week, I’m quite happy to have received the single solution that I did, even more so since that solution was a correct one, from John Jairo Vergara Domìnguez, whose offering you can see if you scroll down to the bottom of that link. Thanks again, John, and well done!

As excellent as John’s solution was, it would still require a little tweaking to work for other ranges (part of its construction is dependent on the array in question being in certain columns within the worksheet) and, in any case, I would now like to present the solution that I developed for this problem.

More…

Advanced Formula Challenge #1: Single column from many (containing blanks) (2) – Columns first 3

We saw in a previous post (here) an outline for a solution which, given a two-dimensional array, potentially containing some empty cells, generated a list of all non-blank entries from that array in a single column.

In that solution the returned entries were listed in an order which is consistent with the entries from an entire row from the original array being returned prior to moving onto those in the next row. The converse, in which entries are returned in a columns-first fashion, is the challenge I would like to set for any readers of this post willing to have a go.

More…

Single column from many (containing blanks) (1) – Rows first 20

Given a two-dimensional array, potentially containing some empty cells, it is sometimes desirable to create a list of all non-blank entries from that array in a single column.

In general, it is not a major concern in which order the returns appear in this new column, and indeed the “standard” solution for this problem is the one given here, in which those returns are listed in an order which is consistent with the entries from an entire row from the original array being returned prior to moving onto those in the next row. The converse, in which entries are returned in a columns-first fashion, will be the subject of my first Advanced Formula Challenge post to follow this one.

More…