Splitting my data

so basically i have a very large set of data and it is basically a list of results. for example if a random number generator was to continually choose a number between 1-5 I have the list of results looking like this:
row1: 4,4,1,2,1,5,4,3,2,1,4,3,2,4,1
row2: 5,2,1,5,3,1,5,1,3,4,2,1,4,2,2
etc.

In my case only one outcome value is important to me (lets say "3") and even more important is the length between each occuring and i want to change it to look like this:
row1: 4,4,1,2,1,5,4,3
row2: 2,1,4,3
row3: 2,4,1,5,2,1,5,3
row4: 1,5,1,3
row5: 4,2,1,4,2,2
etc.

i currently have the cells of value to me highlighted so it sticks out but that's just not going to cut it and would take me waaaaay too long to reformat this information manually. I do not want to change the order, I only want it to put everything on the next column everytime said cell appears. please can anyone help me to figure this out.

How is the number at which the data should stop decided?

No, what i meant is how will the code know which is the number it needs to stop at? Should i have an inputbox there to ask the users for the number and then proceed accordingly?

Ok, i meant to ask you - do you want a popup to ask the user which is the number? So if the user enters 3, it creates a new line for every entry once it encounters 3? Or if the user enters 4, it creates a new line for every entry once it encounters 4?

Are the numbers in different columns or in 1 cell per row?

What about the raw data - for e.g - row1: 4,4,1,2,1,5,4,3,2,1,4,3,2,4,1 is this in separate cells, like A2 has 4, B2 has 4, C2 has 1, D2 has 2, etc?

Try this:

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Make sure the data is onscreen starting in row 1, then press Alt-F8 and select it from the macro list.

It's a macro. Edit it... change the default from 3 to "T" or whatever you want. Or leave it 3 and put T in when popup occurs. Shouldn't matter, but I assume you've tested that already and found that out.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

I'm having some issues with actually implementing the macro. the only thing that happens when i run it is the cursor changes to the loading symbol (for most it's a blue circle continuously rotating) and excel becomes unresponsive. i've waited several minutes before going into task manager and it was still unresponsive and displaying the loading cursor symbol. any ideas what the issue is? i've noticed that it says compatability mode on the window bar above not sure what that means though. also it is excel 2007 so idk


Edit: i'm not sure if this would be a factor but each cell is 1rowX1column and there are 1000 rows with 90 columns each but 5-15 blank columns per row. 90,000 cells in total including blanks. should that cause it to take longer than the 10 minutes i waited before using task manager?

I can't say. You can create a new workbook and drop 20-30 rows of your data into it, then test on that.