Splitting my data

[andrew16]

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.

[andrew16]

I see many views but no replies... is my problem not easy to fix or something?

[arlu1201]

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

[andrew16]

pretend it's a lottery and i only win on "3". each row is the results until 3 comes and then i could see how far in between each and asses minimum span, maximum span average, etc

[andrew16]

in theory 1,2,4,5=L and 3=w and data would in turn look like:
row1: L,L,L,L,L,W
row2: L,L,L,W
etc

[arlu1201]

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?

[andrew16]

i don't know anything about code and this is my first time with excel. I just want to find out how many cells are in between each conditionally formatted cell.
to change 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

to 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

[arlu1201]

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?

[andrew16]

I see, no it's not needed I'm only needing to analyze the one result

[arlu1201]

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

[andrew16]

each result is in it's own cell so there are many columns per row and not necessarily the same amount of columns per row either

[arlu1201]

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?

[andrew16]

yes that is correct

[JBeaucaire]

Try this:

Option Explicit

Sub SplitColumns()
Dim MyVal As String, strFIND As Range, strFIRST As Range

MyVal = Application.InputBox("Please enter the number to split by:", "Split String", 3, Type:=1)
If MyVal = "False" Then Exit Sub

Application.ScreenUpdating = False
On Error Resume Next
Set strFIND = Cells.Find(MyVal, LookIn:=xlValues, LookAt:=xlWhole)

If Not strFIND Is Nothing Then
    Set strFIRST = strFIND
    Do
        If strFIND.Offset(, 1) <> "" Then
            Rows(strFIND.Row + 1).Insert xlShiftDown
            Range(strFIND.Offset(, 1), strFIND.Offset(, 1).End(xlToRight)).Cut Range("A" & strFIND.Row + 1)
        End If
        Set strFIND = Cells.FindNext(strFIND)
    Loop Until strFIND.Address = strFIRST.Address
End If

Application.ScreenUpdating = True
End Sub

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.

[andrew16]

that seems to be what I need except that in my case the data isn't actually numbers. it is the letter "T" as all my data is represented by single letters rather than numbers

[JBeaucaire]

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.

[andrew16]

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?

[JBeaucaire]

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