Creating Detailed list from Summary data

**shufflyman** · 07-10-2012, 06:08 AM

Hello everyone, my first post.

I have a summary list and I am looking to produce a detailed list by individual item from the summary. For example:

Summary list (this lists the number of each type - s1, s2 etc)

s1: 1
s2: 2
s3: 3

Detailed List

s2
s3
s1
s3
s2
s3

Is there a function that will produce the detailed list from the summary one? Secondly if such a function does exist am I able to specify criteria for the order in that list - e.g. there should not be two consecutive s2's

Thanks in advance!

**arlu1201** · 07-10-2012, 06:32 AM

I am not sure if this can be done by a formula but it can be done through VBA.

How many such conditions do you have where the nos should not be consecutively placed?

**shufflyman** · 07-10-2012, 06:35 AM

Thanks for the reply.

I've got a few conditions - There shouldn't be consecutive types or within 2 places, some types shouldn't be next to others etc

**arlu1201** · 07-10-2012, 07:16 AM

We can have the macro create the summary and then maybe use a rand function in a helper column to get the random placements.

Is this data
s1: 1
s2: 2
s3: 3

in column A? Where do you want the output to be displayed?

**shufflyman** · 07-10-2012, 07:18 AM

Not sure I understand your last suggestion. The summary is where I input, and the detailed list is effectively the output

**arlu1201** · 07-10-2012, 07:19 AM

Yes, but where do you want the output? In a separate sheet or in a separate column?

**shufflyman** · 07-10-2012, 08:00 AM

Just in a seperate column is fine

**arlu1201** · 07-12-2012, 12:22 PM

As per my suggestion in post 4, we have to break up this solution into 2 sections.

The first section is getting the values replicated as you needed - use this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

*************************************************

Regarding part 2 of your solution, now we need to randomize the values we have obtained.

I am not sure if i am 100% right on this one, but i think that after populating the values in column D, we can use the =rand() function to randomize the numbers. Once you have the random values in the adjacent column (column E) in this example, we can sort the values in column D to make them fit your conditions.

If it does not work, feel free to post a question in a new thread about the random part

as i dont have much exposure on that side of it.