Hello to all,
I have been trying to learn and understand a bit more of excel for the last year, and I think I now have some minor / medium knowledge. I have always found the solution by testing and doing research but this time I could not find anything that would solve the problem exactly as I need it so I had to post it and ask for help to this lovely community.
I am an Engineering and Production Manager (non-software related) and in my company the "production system" is very (for the lack of a better word) archaic, the volume of production changes constantly and the options which apply to certain part numbers go in the range of thousands, so optimizing labor is a task of exhausting hard work and there is not a systemic approach to it. As of right now I have created a tool that helps my employees a lot, but to finish this master piece (I want to call it that way ) I am still missing one thing. Which is what I am asking for help here, so here we go.
I have a "dynamic size list" that could have 3 - 180 components, maybe even more, this components are "work loads for each production operator" and we will name them with a letter on this example, so for this list I need to create all possible combinations that could exist. Lets say I have 4 operator work loads, first called A that has a 49% value, B that has a 37% value, C that has a 51% value and D that has a 30% value. I want to create a matrix that has all possible combinations so then I can add everything up and automatically with some formula choose the one closest to ~93% (I am not worried about the formula I am confident I can come up with that one easily, the thing I have no idea of how to do is how to create the matrix)
I know this could be solved with VBA, I am really not very good with it and I am pretty sure that my people hate working with excel files that have MACROS, since I am doing this for them and they will be the users of this I would prefer a simple formula, but if this is not possible with formulas I would still appreciate a VBA code.
Let me try to put an example of what I need.
I have the following list
A B
I would like to obtain on a different range the following matrix
A B A B
Another one:
A B C
The matrix I need would be:
A B C A B A C B C A B C
Last example just to make sure it's fully clear.
A B C D
The matrix would be:
A B C D A B C A B D A C D B C D A B A C A D B C B D C D A B C D
Remember the number of components could be VERY HIGH, I don't think I would really reach more than 100 but it's possible, so the number of combinations and the size of the table could be EXTREMELY big)
The order in which the combinations are displayed DOES NOT MATTER but each component must be on 1 cell, NEVER 2 or 3 components of the list in the same cell of the matrix.
Thanks a bunch to everyone and hopefully I can find a solution to this issue.
Bookmarks