Advanced Formula Challenge #12: Results and Discussion 2

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

Such was the number and variety of responses to this challenge that presenting a detailed breakdown of one such solution – as has been the case for all of the first eleven in this series of challenges – would, I feel, be somewhat inappropriate.

For the majority of these challenges, it could be argued that there has been one solution which is indisputably “better” than the rest. Perhaps such an adjudication can also be made here, though to do so would certainly not be a straightforward exercise. What’s more, to pick just one of the many solutions would be to leave the rest – unfairly in my opinion – left on the sidelines.

As such, I would refer the readers to the many solutions in that post and to enjoy dissecting the varied and wonderful constructions therein. And to simply thank all those – Alex, aMareis, Maxim, John Jairo, sam, Jeff, Lori, Ron, Michael, Christian and XLarium – whose excellent contributions led to such a fruitful and inspiring discussion.

There’s evidently still much to be discovered in the world of worksheet formulas!

Another challenge to follow shortly. Watch this space!

Shortest Formula Challenge #4: Results and Discussion 4

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

A good response to this one, leading to a solution for which, in the end, most people who responded can take some credit.

Snakehips started the ball rolling with a nice logical construction involving “OR”ing two separate COUNTIFs; John Jairo V then shaved off several characters from this solution; this was then further refined by Elias; and, finally, after several attempts at constructing a solution using FREQUENCY, Alex Groberman took the COUNTIF set-up and wrapped it in that most wonderful of functions – MODE.MULT – to give us our winner.

More…

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

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…

Grid of Random Integers 3

Inspired by a recent query at one of the Excel forums I occasionally visit, I would like to share a formula-based solution for the task of generating an nxn grid of random integers, where each of those integers is unique within that range.

For example, for the case of n=10, we might have, in A1:J10:

Grid of Random Integers

where I have formatted the cells in this range as custom type: 00 (applying a TEXT function to the formula would complicate matters, in the sense that this would interfere with the functioning of our FREQUENCY construction).

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…

Extracting numbers from a string 3: All numbers to individual cells 28

This is the third in a series of discussions on the techniques available for extracting numbers from an alphanumeric string.

In the first instalment in this series (which can be found here) I looked at extracting consecutive numbers which appear at the start of the string, e.g. 123ABC456.

In the second instalment (here) I looked at extracting consecutive numbers which appear at the end of the string, e.g. 123ABC456.

In this post I will demonstrate a technique for extracting all numbers from a string where:

  • The string in question consists of a mixture of numbers, letters and special characters
  • The numbers may appear anywhere within that string
  • Decimals within the string are to be returned as such
  • The desired result is to have all numbers returned to separate cells

More…

Collating from multiple sheets based on conditions 5

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 3

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 2

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…

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

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…