Reply to thread

Message: [QUOTE="Randy, post: 6393783"] I wonder whether anyone is so inclined to offer their suggestion as to how one might accomplish the following... (A snippet of code for a macro is great by the way, if anyone thinks they might have an idea.) I'm using Excel 2007, and am at a sort of junior moderate level of proficiency with the VBA after a good number of years at it on a part-time basis. DEFINITION of issue: I would like to use Excel to populate data into individual cells corresponding to three individual (and unrelated) headings, and once finished, enter a simple keystroke (ie. execute a macro) that would produce an output of every possible combination the data in the cells inputted under each of the headings. The icing on the cake of this plan would be if the input interface for this process could be presented to the user in the form of a three-dimensional look with three axes (although I don't know if Excel 2007 can do it). However, the three dimensional look is a luxury designed to enhance user friendliness... and in fact a two dimensional, 'good old normal Excel look' is just fine. ANALOGY of problem: Imagining a three dimensional object – for instance a cube - would be the best way to describe an ideal input interface for this, and to really understand the idea here. Were it possible (and perhaps it is not) to have Excel present cells by width (on a y-axis, that is), AND by length (on an x-axis) AND by height (on a z-axis), with corresponding rows (or columns, whatever the case may be) of cells running along each axis, the user would have a friendly interface to use to populate cells along each axis, until he's inputted all of his data. At the conclusion of the inputting, the data in each cell along each axis is processed and consequently (after selecting a keystroke, that is) outputted with every possible combination displayed in a single column of cells showing the individual results, separately (one result per cell), as the output, on either a separate worksheet or new workbook. SPECIFICS The input process... 1) The user begins entering data in the second cell along each axis (eg. A2). 2) The user can enter any combination of alphanumeric data in a cell, and/or leave spaces. 3) The user must not skip a cell along any axis (eg. By filling in A7 and A9, but leaving A8 blank). 4) The user may elect not to not input data in the cells running along one axis (with the result, for instance, that cells along the x-axis and z-axis are populated with data, yet no cells along the y-axis are populated). 5) The user may input data in more or less cells along one axis than along another (eg. user inputs data in three cells along the x-axis, 2 cells along the y-axis and 4 cells along the z-axis). The output... 1) The data inputted into each cell is presented, in output, in every possible combination with the data from the other cells. 2) A single space is left between each set of data, in the outputted format (ie. Tree Car Blue, not TreeCarBlue) 3) The integrity of the data in the cell itself is preserved (ie. if “Tree” is the input data, it is not then outputted in a shorter form such as “Tre” or “T”, in addition to being outputted as "Tree") 4) The combinations in the output ought to include using the data in a cell under one heading (ie. along one axis, that is), and: a) placing it in front of the data in each of the cells under the same heading; b) placing it behind the data in each of the cells under the same heading; c) placing it in front of the data in each of the cells under each of the other headings; d) placing it in behind the data in each of the cells under each of the other headings; e) placing it between the data from each of the other cells under the other headings; .... and any other possible combining pattern that one can think of to produce the result that every possible combination is shown in the output. 5) There ought not to be duplicate entries of exactly the same output. EXAMPLE (of inputs, and some of the resulting output): Inputs: Along the cells on x-axis: X2: Tree X3: Flower Along the cells on y-axis: Y2: Car Along the cells on z-axis: Z2: Blue Z3: Yellow Output (in first column of fresh worksheet or workbook): A1: Tree Flower A2: Flower Tree A3: Tree Car A4: Car Tree A5: Tree Blue A6: Blue Tree A7: Tree Yellow A8: Yellow Tree A9: Tree Flower Car A10: Tree Car Flower A11: Tree Blue Car A12: Tree Blue Flower Car A13: Tree Yellow Flower Car A14: Tree Blue Yellow Car Flower A15: Car Yellow Flower Tree Blue etc… (until all combinations are shown) Many thanks, by the way, to the highly skilled MVPs and other persons of great intellect who have made this group a success for so many years now. You guys are great and I truly aspire to such greatness. It's no easy feat! [/QUOTE]

Verification