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

Assuming your data are on Sheet1, starting in A1, you could create a new sheet and insert the following formula in A1:
=OFFSET(Sheet1!$A$1,(ROW()-1)*3,)&" by "&OFFSET(Sheet1!$A$1,(ROW()-1)*3+1,)
Copy that formula down as far as needed. No code required.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul,

That was magic!! I'll study that formula and learn a lot. Been using EXCEL since '93 but that was above my pay grade, lol! Thank you!!

Rusty

<email address deleted>
PS: if you're a reader, are a sci-fi fan and have a kindle or nook I'll send you the first two books of my four book series (book three coming out next year). just email me at above address (if this forum lets me post links) and let me know which eReader you have and I'll email them to you.

Cross-posted at: http://www.mrexcel.com/forum/excel-q...-manually.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Got it...sorry about that.