Need Macro to select and copy data, paste it to a new location, then delete the old row

Hi-

I am currently trying to organize data for our research lab and would really appreciate some help. We currently have a program that generates an Excel spreadsheet of test data that we need to reorganize a bit.

Each mouse the program monitors has a unique number, and its movement is measured through a series of trials. Unfortunately, the program creates a new row for every trial period instead of keeping each mouse's data on the same row. So instead of something useful like Animal1 - Movement Time 1- Movement Time 2.. etc in one row, the program generates a separate row for every Time Period and we end up with 7 rows of data for each mouse.

What I would like to do is create a macro that sorts the information by Animal Number (got that part down!), checks to see if the following row is a duplicate animal number, and if it is, selects data in columns I through N, pastes the selected data on the end of the first row containing that Animal Number, and then deletes that entire row. Basically:

1. If Animal Number = Duplicate
2. Copy I to N of row below first original Animal Number
3. Delete entire row containing duplicate Animal Numbers
4. Repeat with remaining animal numbers until Animal Number cell is blank

I have attached an example of this worksheet, the first two rows show where I manually deleted the duplicate information and pasted it onto the end of the matching Animal Number.

We do research to alleviate Parkinson's seizures, so it's for a good cause and I would really appreciate the help!TFC Ethos 080612.xlsx

Maybe:

Please Login or Register  to view this content.

Wow. Not only was that a super-fast reply, it works perfectly. I can't thank you enough!!

I'm sorry, but I spoke too soon. The macro doesn't seem to be deleting the top two entries after it finishes editing the list, and I cannot get it to work on larger files. The size of my data files will adjust, so is there a way to get this macro to run of files with varying record numbers? Here is the spreadsheet I'm having trouble getting it to work on.

Thanks again for your help!

Maybe:

Please Login or Register  to view this content.

That seems to take all the data on the sheet and move it to the first row, leaving me with just one line of data The first macro was so close, I just can't figure out how to tweak those issues.

Hi John-

Each Animal Number should have 5 rows of data, one for each trial period; NT1, NT2, NT3, TI and T2. I see now that when I copied and pasted the data from our excel spreadsheet into the one I posted, it changed the formatting of the time periods (no clue why or how it did that) thus making it appear that each Animal Number has 10 rows of data instead of 5. I did not catch this error before, I'm really sorry about that!

I re-uploaded the file exactly the way it should look at the point where we will need the macro. It's SO CLOSE to working!

Maybe:

Please Login or Register  to view this content.

Hi Mike-

I was still trying to get your previous macro to work, sorry for the delay in reply. Using your most updated macro, there should be six columns of data moved from each row, so NT1 has seven values (Total Distance Moved, Distribution, Duration, Frequency, Latency to First, Moving, Not Moving), NT2 has seven values, NT3 has seven, etc. However it only appears to be copying over 4, skipping a column, copying 5, skipping a column, etc.

Oh- Just saw your update. Now I am not getting the empty columns, but am still missing several columns. I'm so glad you're out there. This has been driving me up the wall for two days now!!

Six values, not seven. I need coffee. But yes, adding values from columns H to M.

I'm using the same workbook I uploaded for testing. Where the value of Animal Number is in column C. There seem to be six columns missing, I'm not being as thorough in checking this as I don't want to make you wait long for my reply.

Ok, I went through and deleted all the macros I had stored in my personal workbook and tried it again. I think I was attempting to use not your latest, but the one JUST before. It works. OMG it works!!!!!!

Thank you so much, I am so grateful!! You rock! Let's mark this Solved, shall we?