+ Reply to Thread
Results 1 to 5 of 5

Separate data into columns based on the value of one column?

  1. #1

    Separate data into columns based on the value of one column?


    I am trying to separate data so that I can easily perform an analysis on only a portion of it. The data is arrange so that one column contains the # of the particle, one column contains the elapsed time, and the third contains the velocity of the particle. Each data set has multiple particles that are have multiple time points (like 1000).

    I would like to create separate files for each particle, that contain the particular particles time and velocity that is named based on the particle name. Alternatively if it would create separate columns containing the above information that would also work well.

    After manually separating the data a few times, I realized that there must be a easier way to do this. Does anyone have any suggestions?

    Thanks for your assistance

  2. #2
    Forum Contributor
    Join Date


    You need to attach a sample file in zip format.

  3. #3

    Here is a simplified version as a sample.
    Attached Files Attached Files

  4. #4

    Any answer?

    Does anyone have an answer for how to do this? Because I have the exact same issue.

  5. #5
    Forum Contributor
    Join Date
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Unfortunately I can't open attachments so I'm working a bit in the dark about your exact format.

    Is the data for each particle all in a block followed by all the data for another?
    Are the times all in strictly ascending order with a fixed increment?

    Anyway it should be possible to solve your problem using MATCH and INDEX.
    These two functions can be combined to produce a rather more versatile version of the function VLOOKUP.
    Assuming your data is in Sheet1 and you are moving it to Sheet2
    Sheet2!D1 : enter a particle number.
    Sheet2!E1 : =MATCH(D$1,Sheet1!A:A,0)
    Sheet2!E2 : =MATCH(D$1,OFFSET(Sheet1!A$1,E1,,10000),0)+E1
    Replicate E2 down.
    Sheet2!A1 : =INDEX(Sheet1!A:A,$E1)
    Replicate across to column C and then down as required.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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