Eugene, Oregon edition. My flight from Salt Lake to Eugene was canceled. Instead I got to go to Seattle and sit in the airport for three hours before taking a puddle jumper to Eugene. They said the cancellation was due to maintenance on the airplane. I don’t know what that means, but I have the feeling it means they didn’t sell enough seats. Maybe I’m just pessimistic. Instead of getting here at 1PM, I got here at 6PM.
I rented a bike from the hotel and checked out the much-touted Eugene trail system. It did not disappoint. I rode 15 or 20 miles which allows me to eat and drink guilt-free all day.
It rains a lot here.
We took a tour of the University of Oregon athletic facilities. We got to walk out on the field which is made of field turf. I don’t think I’ve ever felt field turf before and it’s pretty cool. Every football field has a crown that helps the water drain off. It’s hard to notice from the stands or on TV, but when you’re down on the field, it seems huge.
This week’s Excel question comes from Timothy:
I have a list of contacts that I need to copy over from Word to Excel. Each contact has 16 headers (first name, last name, email, phone etc.) but when I import it into Excel I only get two columns of data that repeats itself. For example column 1 line I is: First Name and column 2 line 1 is: John etc.
until column 1 line 17 that stats all over again with First Name: and then column 2 is: Mike.How do I shift the column headers from rows to 16 columns going across the top and transfer the data into the relevant cells.
Have a great weekend.
If I read this right
Assuming that Column 1 has the Field Name (Name, Address etc) extended repeatedly down in blocks of 16 and
Column 2 has all the Field values for the Field names in Column 1
I would manually Copy and Transpose the headers from A1:A16 into C1
and then in C2 I would use an Offset formula like
=+OFFSET($A$1,((ROW()-2)*16)+(COLUMN()-COLUMN($C$1)),1,)
and then copy that across to R2 and then copy C2:R2 down until you have collected all your data
Hui…
and don’t forget to Copy and Paste Values before you do too much else
Hui…
That problem looks like:
http://www.rentacoder.com/RentACoder/misc/BidRequests/ShowBidRequest.asp?lngBidRequestId=564367
“The attached spreadsheet has a list of names with the first name, last name, title, company name and country for each entry all in a single cell.
I need you to divide the information in each row across the appropriate columns. See the first name in the spreadsheet for an example.”
I think this week’s question can be solved by downloading my PUP v6 add-in. The Transform Vertical Range utility should do the job.
Download it here:
http://j-walk.com/ss/pup/pup6/trial.htm
Click here to see the Transform Vertical Range dialog box:
http://j-walk.com/ss/pup/pup6/images/transformverticalrange.gif
I am a big fan of your books and had been using code I discovered in one of your books to remove a macro from an Excel workbook before distribution in earlier version of Excel.
I recently tried to use this code in Microsoft Office Excel 2003. I get the message “Programmatic access to Visual Basic Porject is not trusted”. Is there another method for removing modules (macro) before distribution or to make them not accessible after a save to another filename.
I loved this tool. I am assuming that the security changes are limiting use of these methods.
Sub RemoveModule()
Dim iLines As Integer
Application.DisplayFormulaBar = False
ActiveWorkbook.Save
iLines = ThisWorkbook.VBProject.VBComponents(“ThisWorkbook”).CodeModule.CountOfLines
ThisWorkbook.VBProject.VBComponents(“ThisWorkbook”).CodeModule.DeleteLines 1, iLines
Set VBP = ActiveWorkbook.VBProject
With VBP.VBComponents
.Remove VBP.VBComponents(“Module1?)
.Remove VBP.VBComponents(“Module2?)
.Remove VBP.VBComponents(“Module3?)
.Remove VBP.VBComponents(“dateentry”)
End With
‘Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & ThisWorkbook.Name
End Sub
Brenda: http://support.microsoft.com/kb/282830
And here’s a way to have your macro check to see if programmatic access is allowed (and avoid the cryptic error message):
http://www.j-walk.com/ss/excel/tips/tip96.htm