Move selected cells to previous row

I have a sreadsheet like this (shown in CSV format), Only the first column is really relevant.

1,a,b,c
2,d,e,f
3,g,h,i
3R,j,k,l
4,m,n,o
5,p,q,r
5R,s,t,u

I want to detect all rows with the R on the end of the text in the first column and shift that row to the end of the preceding row and delete the "R" row, producing this:

1,a,b,c
2,d,e,f
3,g,h,i,3R,j,k,l
4,m,n,o
5,p,q,r,5R,s,t,u

Is there any easy way of doing that automatically please? There will be many thousands of rows so I don't want to have to do it manually.

Can you correct your example? It does not appear you DID what you said, which would be take line 5 and remove the ,r at the end and then move the remaining string UP into the row above. You appear to have just merged rows 5 and 6 and left the ,r in there.


Even better, make a longer example and upload it in an Excel sheet since that's where we'll do the work.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Use BEFORE/AFTER examples just like you did above, but make 100% sure it is the right results.

What I said is a bit confusing. I also don't really want the R cell included but thought that would make it simpler.
Attached is a file showing what I really want.

I have repeated the data in the file for the example only. I only want the one lot of data after the macro runs.

I also had to change the unique rows to use eg. 40.1 instead of 40R. I discovered that Excel does not sort them one after the other even if I change all cells to text (Windows Explorer sorts files named like that correctly).

convert.xls

TO do it "automatically" means using a macro.

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. Press Alt-F8 and select it from the macro list.

That is brilliant! Thank you very much for the assistance.

Oops. One slight problem when I run it for real. My spreadsheet has various header and comment rows before the data. As a result the macro produces a Type Mismatch error.

I updated it as follows and it is now fine:

Please Login or Register  to view this content.

I have marked this thread solved for you.
In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

I've run into another problem. It's my fault for not giving all the information. The spreadsheet actually contains values in columns H onwards. When I run with no data there, like in my original sample file, it works fine. When I have data there it only works for for the last of the "special" rows. The special rows get deleted but their data does not get put in columns E-G.

Ended up changing to this. I'm sure the 3 assignment statements can be combined but I could not figure out how to combine them.

Please Login or Register  to view this content.