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

rustywriter

New Member
Joined
Dec 2, 2016
Messages
3
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

rustywriter

New Member
Joined
Dec 2, 2016
Messages
3
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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...


Code:
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
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Please note that post #3 has been edited
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Code:
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

New Member
Joined
Dec 2, 2016
Messages
3
Apologies, I wasn't thinking straight on the last code. Please test with the code below :oops:

Code:
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
...and with an answer the OP seemed happy with almost 2 hours before posting here. :whistle:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,210
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top