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:
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).
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!).