How to select rows which have the same value in of the cells and copy them all?

**Petrichor** · 02-24-2017, 03:03 PM

Hello dear folks who have to or chose to use Excel. I am doing my master thesis project which uses pupilometry data and that means I have A LOT of data. What I need to do is to average values of some cells and list them, but these cells are not adjacent..I believe I uploaded an attachement with a screenshot of my Excel sheet, can you guys see it? :\

IF YOU DO NOT SEE THE ATTACHMENT: I need to chose rows which have a particular value (letter and number combo, eg P01) in my column B. These rows are not adjacent, which is why I need to find a way to select them, so I can copy them and paste it and so have them all adjacent. So I am looking for a way to tell Excel 'chose any row which has value P01 in column B and copy it'..Basically I need to find a way to sort my rows based on the value in one of the columns.

IF YOU DO SEE THE ATTACHMENT: Column A has a value Trial005, column B indicates which participant the data in that row belongs to, so data in rows 433 to 442 corresponds to participant P03(column B) Trial005 (Column A). Now..I need participant's P03 data for all trials (Trial006 and Trial007 and so on ) and they are all scattered and some have 2 rows of data per trial, some have 10 etc. How can I copy all rows corresponding to P03 and paste them so that they are adjacent to each other?

Thank you soooooooo much to anyone trying to help, I am running out of time and I must find an easier way to sort the data!

**FDibbins** · 02-24-2017, 03:06 PM

Hi, welcome to the forum

Your pic did come through, but it is really small - and we cant really play with images, a sample file will make things much easier.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Petrichor** · 02-24-2017, 03:54 PM

Hi,

thank you for Your swift response! But I do not understand what is meant by a clean and small sample workbook which is not a pic? I am trying to upload a zoomed in Excel file, let's see if that Works.

The problem is that even this file I uploaded cannot communicate anything because it cannot cover enough data points at all for me to show what I need to do..

So basically I need to sort the rows based on the values in column B. Column A in this example has the same values, that is because I have many data Points. But there are over 80 trials and I need all rows which correspond to those 80 trials (that is way more rows than 80 because for each trial(column A values) for each participant(column B values)I have a few rows) sorted by the value in column B. I am not sure if any of this makes sense cos I don't know how to describe the data without refereing to participants and trials..

But I think that the essence is that: I NEED TO SELECT AND COPY THE ROWS BASED ON THE VALUE IN COLUMN B ( all rows for P01Pilot, then all rows for P02Pilot and so on). And I need to paste it so that all rows for P01Pilot would be adjacent and same for P02Pilot etc.

On the right hand side there is an example how I want data to actually look. But I am not sure if that is helpful at all because these are averaged values for each trial and other trials than Trial001 are way down the sheet (that is part of why I need to sort the data).

So again, I feel the point is that I need a way to tell Excel 'CHOSE ALL ROWS WHICH HAVE VALUE P01 IN COLUMN B AND COPY ALL OF THOSE ROWS'

I feel like there is some very easy solution for that, which would save me a lot of time because I have like 300 sheets with data like this...

Thank you so much for any help kind People!

**jaslake** · 02-24-2017, 06:37 PM

Hi Petrichor

I'm not certain I know what you're looking for. This Code in the attached appears to do as I understand your requirements...Click the Button.

Please Login or Register  to view this content.

**Petrichor** · 02-25-2017, 09:57 AM

Ok..wow. I have not really done excell scripting before, but I feel like the data on the right side of the sheet I uploaded is confusing. Forget that, becaus e AVERAGED PUPIL DIAMETER FOR EACH TRIAL is sth I will get after I sort the data and apply average function. But the point is that to get there I need to firstly sort all the raw data by the value in column B. I feel bad because I think you are telling me somethng really complicated. Sorry to have wasted your brain power if it is sth much more simple that I need!

I just need to tell excel: sort all rows by values in column B (P01Pilot; P02Pilot and so on). Because at the moment they are sorted by value in column A. There are like 800 trials (column A goes up to value Trial800) and 35 different values in column B (up to P35). I need to say 'select and copy all rows which have value P01pilot in column B; then the same for P02pilot and 35 other values which I have all the way down in column B' OR in other words 'group all the rows according to the value they have in column B' I just need my data to look sth like this:

SUBJECT TRIAL average pupil diameter (mm)

P01pilot trial001 3.9
P01pilot trial001* 4.1
P01pilot trial002 ...
P01pilot trial003 ...
P01pilot trial004 ...
P01pilot trial005 ...
................
...............
P01pilot trial800 ...

P02pilot trial001 ...
P02pilot trial002 ...
P02pilot trial003 ...
..............
P02pilot trial800 ...

P03proper trial001 ...
.............
P03proper trial800 ...

....

P35 trial001 ...
....
P35 trial800 ...

*there will be many instances of the same trial for the same participant, so I think that is confusing, but just ignore that, I work with data later to avergae it, for now just need to list it by P01 and P02 and so on

I am sorry I am so hopeless at describing this. I never deal with any data, just psychology data, so it is hard for me to properly describe what I need to accomplish without refering to what the data is.. Please tell me if you feel like there is a particular bit in my explanation that you do not understand. Thank you!!

**jaslake** · 02-25-2017, 08:20 PM

Hi Petrichor

I've gotta admit I'm having a difficult time following your requirements. This Code in the attached will do this...

I just need to tell excel: sort all rows by values in column B (P01Pilot; P02Pilot and so on)

Now, having done that what do you wish to do?

Please Login or Register  to view this content.

**Petrichor** · 02-26-2017, 06:08 PM

Yes, actually this worked! Thank you so much!

I am sorry I am hard to follow, will try to improve my Excel lingo haha!

All the best,

Teja.

**jaslake** · 02-26-2017, 06:22 PM

Originally Posted by Petrichor

Yes, actually this worked! Thank you so much!

I am sorry I am hard to follow, will try to improve my Excel lingo haha!

All the best,

Teja.

Actually, your Excel Lingo was pretty direct...I read too much into it.

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

**jaslake** · 02-26-2017, 07:04 PM

You're welcome...glad I could help. Thanks for the Rep.

How to select rows which have the same value in of the cells and copy them all?

LinkBack

Thread Tools

Rate This Thread

Display

How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Re: How to select rows which have the same value in of the cells and copy them all?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Select last row in column A and select 49 rows up, copy results to A2

Find filled cell in column, select corresponding rows and copy only selected cells/columns

How to select the Max value from 24 hour rows daily and copy the corresponding cells

[SOLVED] can only copy/select visible cells w autofilter on &hidden columns, want to copy all cells

Select cells, then copy those rows to new sheet

Select rows and copy to another sheet.

Select alternate rows to copy

Bookmarks

Bookmarks

Posting Permissions