How to sort rows in Excel without having repeated data together-CodePudding

I have a table of data with many data repeating. I have to sort the rows by random, however, without having identical names next to each other, like shown here:

How can I do that in Excel?

CodePudding user response：

This is just an attempt, because the question might need some clarification or more sample data to understand the real scenario. The main idea is to generate a random list from the input, then distribute evenly by names. This ensure no repetition of consecutive names, but this is not the only possible way of sorting. There may be additional combinations, but this a valid one. The solution is volatile (every time Excel recalculates, a new output is generated) because RANDARRAY is

Explanation

LET function is used for easy reading and composition. The name idx represents a random sequence of the input index positions. The name rRng, represents the input rng, but sorted by random. This sorting doesn't ensure consecutive names are distinct.

In order to ensure consecutive names are not repeated, we enumerate (nCnts) repeated names. We use a MAP for that. This is a similar idea provided by @cybernetic.nomad in the comment section, but adapted for an array version (we cannot use COUNTIF because it requires a range). Finally, we use SORTBY with input argument by_array, the map result (nCnts), to ensure names are evenly distributed so no consecutive names will be the same. Every time Excel recalculate you will get an output with the names distributed evenly in a different way.

CodePudding user response：

Perfect case for a recursive LAMBDA.

In Name Manager, define RandomSort as

=LAMBDA(ζ,
    LET(
        ξ, SORTBY(ζ, RANDARRAY(ROWS(ζ))),
        λ, TAKE(ξ, , 1),
        κ, SUMPRODUCT(N(DROP(λ, -1) = DROP(λ, 1))),
        IF(κ = 0, ξ, RandomSort(ζ))
    )
)

then enter

=RandomSort(A2:B8)

within the worksheet somewhere. Replace A2:B8 - which should be your data excluding the headers - as required.

If no solution is possible then you will receive a #NUM! error. I didn't get round to adding a clause to determine whether a certain combination of names has a solution or not.