Excel - Moving rows 3

[JeanW]

I have an old spreadsheet where the data are in the following pattern (all the data is in colomn A):

A1 = definition
A2 - empty
A3 - description
A4 - empty
A5 - example
A6 - empty
A7 - definition
A8 - empty
A9 - description
A10 - empty
A11 - example
A12 - empty
A13 - definition etc....

I need all the definitions in colomn A, all the descriptions in column B and all the eaxamples in column C.
Final outcome should be on row level
Definition - Description - Example in 3 columns.

With all the empty rows I have 38000+ rows.
I know nothing about VB or macro's, so please not too technical, or with enough details so I can try something out.

TIA

 

[SkipVought]

Hi,

Make a helper column B, for a sequence number. use AutoFill to sequentially number each row quickly. This will ensure that you don't get data out of sequence.

Now select columns A & B and sort on column A. This will sort the empty rows together. Delete the empty row data (including the sequence numbers.

Sort back into sequence on column B

Copy the DATA in column B -- Paste into C1

Select the DATA in columns A & B and DRAG the selection DOWN ONE ROW.

in D1: =MOD(ROW(),2) and copy down for all rows

Turn on the AutoFilter -- Data/Filter/AutoFilter

Select 0 in the column D filter.

Copy the data

On a separate sheet, Edit/Paste Special -- VALUES

Delete the columns containing HELPER data.

VOLA!





Skip,

_[red][/red]

 

[JeanW]

Thanks, will take me a while before I have that right.

Best will be to try this on a copy....

Will let you know how it went...

Thanks

 

[Gavona]

First,(save your file then) delete the blank rows:
Click on A to highlight the entire column

Edit,Goto,Special,Blanks

Edit,Delete,EntireRow

Simple formulae:
B1: = A2
C1: = A3

Fill down these formulae

Edit,Copy
Edit,PasteSpecial,Values

Now you can safely delete the rows that you do not need.
Insert a new column A
Fill the first 3 cells in this column with with A, B, C
Fill this down your worksheet.

Now use autofilter to hide all the rows with "A"
Select the visible rows and delete them as above.

Remove the Autofilter

Post back if any of these instructions are not clear. None of the steps are onerous/time consuming.


Gavin

 

[Gavona]

Lots of ways of doing this of course. Having seen Skip's approach i would modify mine:
Rather than using formulae, just copy column A to B and then to C.
Then highlight B1 right-click and choose delete (when prompted choose "shift cells up")
Similarly select C1 and C2 and delete them.

Then you just need to delete the rows you don't need.


Gavin

 

[AnotherHiggins]

Here is a macro (VBA) solution. Don't worry, I'll be gentle.

As has been stated, it is a good idea to fiddle with a copy of the data just in case anything goes wrong.

First, here's what to do with the code below:

[ul]
[li]Go to Tools > Macro > Record New Macro[/li]
[li]Change the Macro Name to HereItIs[/li]
[li]In the Store Macro In section, choose Personal Macro Workbook[/li]
[li]Go to Tools > Macros > Stop Recording[/li]
[li]Go to Window > Unhide > Persnal.xls > OK[/li]
[ul][li]Note: If Unhide is not available, just skip this step[/li][/ul]
[li]Press [Alt]+[F11] to open the Visual Basic Editor[/li]
[li]Press [Ctrl]+[R] to ensure that the Project Explorer is open[/li]
[li]Find Personal.xls - It will look like this: VBAProject (PERSONAL.XLS)[/li]
[li]Under that there should be a folder called Modules[/li]
[li]In the Modules folder, there will be one or more modules named like, Module1, Module2, etc.[/li]
[li]When you double click on a Module, look in the window to the right to find the Macro name. It will look like Sub HereItIs[/li]
[li]Try to find the macro you just recorded[/li]
[ul][li]There might only be one module, or there may be many - it depends on if you have ever recorded to Personal.xls before. If you have trouble finding the Macro you just created (Again, look for Sub HereItIs in the window on the right) don't worry - just right click on the Modules folder and select Insert > Module[/li][/ul]
[li]Click anywhere in the window on the right[/li]
[li]Press Ctrl]+[A] to select all the text in the window[/li]
[li]Now copy and Paste the code below into the window[/li]
[/ul]

Sub InsertAnyNameYouWantHere()
'   Deletes all empty rows
columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'   Loops through cells in column B
For Each Cell In Range("B1:B" & ActiveSheet.UsedRange.Rows.Count)
    '   sets the value in column B equal to the cell 1 row down, 1 row left
    Cell.Value = Cell.Offset(1, -1).Value
    '   sets the value in column C equal to the cell 2 rows down, 2 rows left
    Cell.Offset(, 1).Value = Cell.Offset(2, -1).Value
    '   Deletes the 2 rows below the one being manipulated
    Range(Cell.Offset(1), Cell.Offset(2)).EntireRow.Delete
Next Cell

Now just press the play button at the top of the window.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[JeanW]

You guys are super.

To make everybody happy, I tried each and every suggested answers and they all worked ok.

Well...being a database developer I'm not soo brave on spreadsheet level, so it took me a while.

I learned something from each one of you...

So, to avoid jealousy, everybody a STAR, 'cause that's what you are.....

 

[SkipVought]

All kinds of ways to skin a cat... ;-)

Skip,

_[red][/red]