Working with Excel and even and odd numbered rows

[jcurtiss7492]

I have been given the worst excel spreadsheet I have ever seen and need to clean it up.

Basically, I need to take the data in every even numbered row in Column A and move it to the previous row in column B and then delete the odd row. As the original file is 1200+ lines, I thought AA would be a great solution.

I have attached an example file. At the top is the format of the file currently. At the bottom is how I need it to be.

Thanks in advance...

[trentonponder]

Originally Posted by jcurtiss7492

I have been given the worst excel spreadsheet I have ever seen and need to clean it up.

Basically, I need to take the data in every even numbered row in Column A and move it to the previous row in column B and then delete the odd row. As the original file is 1200+ lines, I thought AA would be a great solution.

I have attached an example file. At the top is the format of the file currently. At the bottom is how I need it to be.

Thanks in advance...

I don't think you need AA for this, you can probably solve it directly with Excel

1 - In Cell B3 enter =A4
2 - Drag the formula all the way down the page
3 - Sort the results by column A
4 - Delete everything below the area in row A where it stops displaying the model numbers.

If you want to send me the complete sheet, I'd be happy to do it for you.

[jcurtiss7492]

Thanks for the suggestion, we will have to clean it by hand as some entries are 2 lines and others are 1, even a couple with 3 lines, ugh

[philkryder]

I looked at your sample and took advantage of the fact that the "secondary" and "tertiary" rows had blanks on the right. In the rows to be kept, the columns on the right were NON-BLANK.

I used that to set a value of 0 if the row was to be kept and 1 if the row was to be combined.

I built formulas to bring the N+1 and N+2 values into two new added columns.

I then copied and PASTED SPECIAL to only paste VALUES not the formulas.

I then moved to a new sheet and sorted by the 0 or 1 so that the rows to be kept were at the top.

quick and relatively painless.
see attached
Phil