Column A, B, C have the values as,

A     B     C
A     A     A
B     B     B
C     C     C
D     D     E
E     E     E

How to randomize within each column so that no row contains same item?

For example, on manually done solution can be -

A     B     C
A     E     C
B     D     E
C     B     A
D     C     E
E     A     B
| improve this question | | | | |
  • Random is one thing. Permutations are another. Be specific. ABCDEF only? A permutation is what you would make from a list of choices. I think you may need to consult the math section first to understand what and how you need to approach this problem. Asking a correct question is important. Otherwise random could mean anything: A, AB, BA, B1C, R3as5d4f6wer – ejbytes May 10 '17 at 1:42
  • I do not understand your question, you can choose from A, B and C yet you have D and E in your random solution? Also you say "no row contains same item" yet I see several of each item in the same row. – Kevin Anthony Oppegaard Rose May 10 '17 at 7:33
  • To clarify the question, I want to shuffle elements in the three columns so that the horizontally adjacent cells do not contain same value. For example (A C E) is acceptable but (A E A) not acceptable. – Amirul Islam May 11 '17 at 4:22

I'm not aware of any "permute column cells subject to constraints" dialogue in Microsoft Excel. As indicated by Burgi, you can write a script to make Execel do exactly what you want.

If you have never written a script or done any programming before, set aside at least a day. It's a huge investment of time but will pay off in the long run. Your new skills will enable you to solve other problems in Excel more efficiently as well.

Start with writing a script that checks whether cell B1 conflicts with cell A1. Then write a script that swaps B1 with another cell from B2 to B5. Then make the swap subject to the constraint that the new contents for B1 resolves a previous conflict with A1. If you want it more random, don't just pick the first cell that fits but make a random pick from all suitable cells. One way of doing this is to first count how many cells fit the constraints, obtain a random integer i in that range and then scan column B again this time stopping at the i-th cell that is suitable for swapping. Then write a loop that iterates over all cells of column B and modify your previous code such that it works not just for B1 but for any other cell in column B. Only swap with cells further down. Swapping with cells that have already been checked would mean that you have to check those cells again and you might end up running in circles, never finding a solution even if a solution exists. Once you mastered this, you will find it easy to extend your script to handle column C as well. Finally, learn how to report error conditions, for example that no suitable cell is available. (6 Es in your example would be a suitable test case.)

| improve this answer | | | | |
  • When down-voting, please leave a comment why you are not happy with this answer. – Joachim Wagner Jul 7 '17 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.