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.
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:
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.
A slightly light-hearted post this, as you may have guessed from the title, though readers might find it moderately interesting, and hopefully some may even contribute to my rather esoteric collection of pandigital numbers in Excel.
This began as a result of seeing (I don’t recall where now, unfortunately) an alternative version of the tried-and-tested construction for returning a number from the end of a mixed string. For example, given the following in A1:
the now-ubiquitous solution:
will correctly return 123456.
The challenge this week is as follows: given a string in A2, return the single-word substring which immediately precedes the only occurrence within that string of a substring of the form XabcdY, where X and Y are either an asterisk or an obelisk and a, b, c and d are integers from 0 to 9.
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.
We can do this with the following set-up: More…
Given the list in A1:A6, where some entries consist of a single number and some of a grouping of numbers (where e.g. 13-16 represents 13, 14, 15 and 16), the challenge this week is to come up with a single formula in D1 which, when copied down an arbitrary number of rows, produces a list of all individual, ungrouped numbers from the list in A1:A6, as here:
The formula is to return a blank in rows beyond the expected number of returns. What’s more, this must be done via reference to a second formula, in C1, also to be derived and whose value is to equal the total expected number of non-blank entries to be returned in column D for any given dataset.
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.
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.
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.