+ Reply to Thread
Results 1 to 18 of 18

Splitting my data

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    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.
    Last edited by JBeaucaire; 12-04-2012 at 09:01 AM. Reason: Corrected thread title to topic only

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: need help sorting my data please!

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

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: need help sorting my data please!

    How is the number at which the data should stop decided?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: need help sorting my data please!

    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

  5. #5
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: need help sorting my data please!

    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

  6. #6
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: need help sorting my data please!

    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?

  7. #7
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: need help sorting my data please!

    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

  8. #8
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Splitting my data

    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?

  9. #9
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Splitting my data

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

  10. #10
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Splitting my data

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

  11. #11
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Splitting my data

    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

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Splitting my data

    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?

  13. #13
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Splitting my data

    yes that is correct

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Splitting my data

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  15. #15
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Splitting my data

    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

  16. #16
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Splitting my data

    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.
    Last edited by JBeaucaire; 12-05-2012 at 10:03 AM.

  17. #17
    Registered User
    Join Date
    12-03-2012
    Location
    vancouver, canada
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Splitting my data

    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?
    Last edited by andrew16; 12-05-2012 at 01:11 PM.

  18. #18
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Splitting my data

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1