How can I delete every 3rd row/add 'by'/combine ever two rows (besides doing it manually)

[rustywriter]

Hi,

I have data that has this format:

<Book Title>
<Author>
<Purchase Date>
(repeat...)

Here's a sample (the first 2 sets where the above format is a set):

The Wrong Side of Goodbye
Michael Connelly
November 22, 2016
The Jewel of Dantenos
Brian D. Anderson
November 16, 2016

It's a long list. I'm hoping I can automate some or all of what needs to be done to it.

What I need is this format:

<Book Title> by <Author>

Example:

The Wrong Side of Goodbye by Michael Connelly
The Jewel of Dantenos by Brian D. Anderson

Ergo, I need to automate the following steps:
1. delete every third row
2. place 'by' after the Book Title
3. combine ever two rows
(I hope that makes sense)

Twenty years ago on a UNIX box I could have done this with awk in seconds but now I just have excel. Any help with any of the steps would be greatly appreciated!!

Old programmer whose forgotten everything,
Rusty

 

[rustywriter]

So sorry...I should have previewed my post before posting. I used some special characters which messed up my post and removed parts of it. I don't see how to edit it or even remove it and redo it. I think/hope you can understand what I'm asking anyway.

Rusty

 

[MARK858]

Assuming that your data starts in cell A1 and based on what I can see in post #1 then try on a copy of your data the code below...

Sub Delete3rdRows()
     
    Dim i As Long, LastRow As Long, DelRange As Range
    Dim x As Long, y As Long, DelRange2 As Range
    
    Application.ScreenUpdating = False
    
    Set DelRange = Range("A3")
    For i = DelRange.Row To Range("A" & Rows.Count).End(xlUp).Row Step 3
        Set DelRange = Union(DelRange, Range("A" & i))
    Next
    DelRange.EntireRow.Delete
    
    For x = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 2
     Cells(x, "A").Value = Cells(x, "A").Value & " by " & Cells(x + 1, "A").Value
    Next
    
    Set DelRange2 = Range("A2")
       For y = DelRange2.Row + 1 To Range("A" & Rows.Count).End(xlUp).Row Step 2
        Set DelRange2 = Union(DelRange2, Range("A" & y))
    Next
    DelRange2.EntireRow.Delete
    
    Application.ScreenUpdating = True

End Sub

 

[MARK858]

Please note that post #3 has been edited

 

[MARK858]

Apologies, I wasn't thinking straight on the last code. Please test with the code below

Sub Delete3rdRows()
     
    Dim i As Long, LastRow As Long, DelRange As Range
    Dim x As Long, y As Long, DelRange2 As Range
    
    Application.ScreenUpdating = False
    
    Set DelRange = Range("A3")
    For i = DelRange.Row To Range("A" & Rows.Count).End(xlUp).Row Step 3
        Set DelRange = Union(DelRange, Range("A" & i))
    Next
    DelRange.EntireRow.Delete
    
    For x = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 2
     Cells(x, "A").Value = Cells(x, "A").Value & " by " & Cells(x + 1, "A").Value
    Next
    
    Set DelRange2 = Range("A2")
       For y = DelRange2.Row To Range("A" & Rows.Count).End(xlUp).Row Step 2
        Set DelRange2 = Union(DelRange2, Range("A" & y))
    Next
    DelRange2.EntireRow.Delete
    
    Application.ScreenUpdating = True

End Sub

 

[rustywriter]

Apologies, I wasn't thinking straight on the last code. Please test with the code below

Sub Delete3rdRows()
     
    Dim i As Long, LastRow As Long, DelRange As Range
    Dim x As Long, y As Long, DelRange2 As Range
    
    Application.ScreenUpdating = False
    
    Set DelRange = Range("A3")
    For i = DelRange.Row To Range("A" & Rows.Count).End(xlUp).Row Step 3
        Set DelRange = Union(DelRange, Range("A" & i))
    Next
    DelRange.EntireRow.Delete
    
    For x = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 2
     Cells(x, "A").Value = Cells(x, "A").Value & " by " & Cells(x + 1, "A").Value
    Next
    
    Set DelRange2 = Range("A2")
       For y = DelRange2.Row To Range("A" & Rows.Count).End(xlUp).Row Step 2
        Set DelRange2 = Union(DelRange2, Range("A" & y))
    Next
    DelRange2.EntireRow.Delete
    
    Application.ScreenUpdating = True

End Sub

Thank you!!
Rusty

 

[Macropod]

Cross-posted at: How can I delete every 3rd row/add 'by'/combine ever two rows (besides doing it manually)
Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[MARK858]

...and with an answer the OP seemed happy with almost 2 hours before posting here.