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

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

Hi-

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?

2. You need to attach a sample file in zip format.

3. Thanks-

Here is a simplified version as a sample.

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

5. 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.

Mark.

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