One for the Excel experts - I need to switch from rows to columns

My name is Richard and I am an Excelaholic!
OK chaps, you win! I have been out and bought 'VBA for Excel Made Simple' by Keth Darlington; one of a series of 'Made Simple' books, (from which I can commend 'Web Pages Made Simple' or similar, for anyone wishing to learn the rudiments of web design). I haven't had too much time to read it yet, though. Too busy with the real work!!!

3 months! How did you hold out that long!?
I taught myself with a "Learn VBA in 21 days" book.

Never actually got to day 21 as I had more than enough to run with before that, but over the years have built & expanded on a couple of add-ins that are regularly used around the firm as well as a couple of large automated models that I expand & update annually.

One was built as part of the learning exercise and I still go back to it every so often to try to refine things.

If you have a repetitive task, and once you have acquired a bit of VBA knowledge, I tend to find that it is quickest to record a macro doing the task and then dive in and edit it to work over the required range / number of times with FOR or DO Loops and counters.

A good source for help online is the Google Group "Microsoft.Public.Excel.Programming" at http://groups.google.co.uk/group/microsoft.public.excel.programming?hl=en

Good luck and persevere.

don't mix up syntax with method
Richard, i have great trouble in remembering vba syntax, but i continue to apply programming methods that i learn't 30 years ago, and the approach is entirely similar for "spreadsheet" solutions - so i take your point about how people apply solutions to problems, but although vba is not same as spreadsheets, problem solving requirements are similar.

vba syntax, on the other hand, is like any other programming language, often unfathomable, never particularly logical, but easily (sometimes!) solved by refering to the help. I bet spreadsheet users have similar problems with formulas they don't use often.

not always obvious when vba is appropriate, but is is a useful weapon to have available.

It may be easy, but...
I agree that the level of genius required to learn the likes of VBA is not too great; when I took over responsibility for our company web site, I managed to teach myself the rudiments of Perl CGI Scripting from a book in a weekend!

The problem I have with such things is that having invested one's time and effort into learning it, one doesn't tend to use it very often, and by the time it is required again, the theory is forgotten. By using non-code solutions, people can use an extension of something that they probably use every day, and when a similar solution is required they may remember, or be able to think through, how it was done.

Glad to be of help!
Still glad to be of help. It wouldn't let me leave this bit blank!

how do you know that you know?
excel is full of lots of lovely stuff. pivot tables, solver, formula, formatting, charts, database, etc, etc, etc.

the great thing is that it is accessible to mr clapham omnibus man, but the problem is that he doesn't know it.

I would accept that the power of vba is in basic programming skills, of a level they teach to teenagers.

The problem with writing a tutorial is that there is lots of paid for competition, and in any case you can pick up lots of freestuff on the web.

However i rekon that if you make the effort to start you will soon get some productivity gains out of it - just like any of the other lovely features in excel - and what could be better than starting with a question on this board, and then using the code supplied, and then trying to uinderstand it

not so much that it would have to be vba, but
that vba provides the best answer. took about 4 minutes to concoct the code, and couple of clicks to run it, which is much less than 15 minutes, even taking into account general relativity and wormholes in space.

can't see why people shy away from vba? its not that difficult.

Alastair and Alan got there just before me
My suggestion was first select the entire range of data and from the menu bar use Data/Text to Columns, using space as the delimiter, and then something like:

Option Explicit
Sub ExpandRows()
Const lLastRow As Long = 300
Const lFirstRow As Long = 1
Const lFirstCol As Long = 1
'assumes starts in column A
Dim lRow1 As Long
Dim lRow2 As Long
Dim lColCount As Long
Dim rRow As Range
For lRow1 = lLastRow To lFirstRow Step -1
Set rRow = Range("A" & lRow1).EntireRow
lColCount = Application.CountA(rRow)
rRow.Resize(lColCount - 1).Insert
For lRow2 = lRow1 To rRow.Row - 1
Cells(lRow2, lFirstCol).Value = _
Cells(rRow.Row, lFirstCol).Value
Cells(lRow2, lFirstCol + 1).Value = _
Cells(rRow.Row, lFirstCol + lRow2 _
- lRow1 + 1).Value
Next lRow2
rRow.Delete shift:=xlUp
Next lRow1
End Sub 'ExpandRows()

A 'code free' but cumbersome solution
For those, like me, who can't be bothered to learn V.Basic, an alternative solution, albeit VERY cumbersome if the data is extensive!
Using Text to Columns, get the data into sheet 1
Name the entire range (e.g.'Table')
In sheet 2, number column A, starting with 2 up to the max. number of cities + 1, (i.e. 6 cities = 2-7)
In column b, enter the first prefix No.(100)repeated 6 times, (or 5 if you're a pedant) to match above.
Replicate this vertically for all prefixes
In column c, type '=VLOOKUP(B1,table,A1,FALSE)'
Copy this formula down for the entire range.
For safety, copy/paste special/values the whole lot, then sort using col.C; delete the blank rows.
Re-sort using B then A to re-sort into original order.

Cumbersome, but it works!

vba i'm afraid!
cannot think of anything other than vba for this!

looks like a mistake in the output, because I am assuming the last 3 should be 101?

if you have a workbook with (at least) sheet1 and sheet2, then put the text stuff into sheet1 starting at cell A1. Then do the text to columns, delimited by space. Then alt & F11 to open the vba editor. Then insert and module from the menu. Then double click on module 1 and paste the code below.

then return to the spreadsheet, select tools, macro, macros from the menu and click on the run button. Sheet 2 should then contain the output you require

CODE

Sub splititit()

Count = 0
Bigcount = 0
Worksheets("sheet1").Activate
Cells(1, 1).Activate

Do While ActiveCell > 0
code = ActiveCell

Do While ActiveCell.Offset(0, Count + 1) <> ""
desc = ActiveCell.Offset(0, Count + 1)
Worksheets("sheet2").Cells(1, 1).Offset(Bigcount + Count, 0) = code
Worksheets("sheet2").Cells(1, 1).Offset(Bigcount + Count, 1) = desc
Count = Count + 1
Loop
ActiveCell.Offset(1, 0).Activate
Bigcount = Bigcount + Count
Count = 0
Loop

End Sub

CODE ENDS

Try this
This assumes that you have copied the text onto Sheet1 and that there is nothing else on that sheet.

It also assumes that you have run "Text to Columns" on the data copied ont Sheet1

The transposed data is set up on Sheet2

If you want to change the input or output sheets change the sheet names in the Set command

Sub ReOrderThem()
Dim shtSrc As Worksheet
Dim shtRslt As Worksheet
Dim RowsSrc 'Number of rows to process
Dim ColsSrc 'Number of columns on selected row to process
Dim counter 'loop counter for rows to process on input sheet
Dim countera 'loop counter for colums to process from input
Dim NextRow
Dim IdxNo

Set shtSrc = Worksheets("Sheet1") 'Input sheet name
Set shtRslt = Worksheets("Sheet2") 'Output sheet name
RowsSrc = shtSrc.UsedRange.Rows.Count 'Number of rows to process
NextRow = 0
For counter = 1 To RowsSrc 'loop on input sheet rows
IdxNo = shtSrc.Cells(counter, 1) 'Hold the index number until move to the next input row
ColsSrc = WorksheetFunction.CountA(shtSrc.Rows(counter).EntireRow) 'count number of entries in selected input row
For countera = 2 To ColsSrc 'loop through colums on input sheet and selected row
NextRow = NextRow + 1
shtRslt.Cells(NextRow, 1) = IdxNo
shtRslt.Cells(NextRow, 2) = shtSrc.Cells(counter, countera)
Next countera
Next counter
Set shtSrc = Nothing
Set shtRslt = Nothing
End Sub

Manual with a few neat tricks
Open the text file so the data is at the top of the spreadsheet.
Select Row 1, copy to the first free row under the original data (maybe leave a spare row for clarity). Say we choose Row 4 (Paste to Row 4).
Now select B4 and do Copy-Paste Special-Transpose.
Select Row 2, copy to the first free row. Paste to Row 10.
Now select B10 and do Copy-Paste Special-Transpose.
Delete Columns C upwards.
Delete Rows 1:3
Select A1 then Ctrl+Shift+8 to select area.
Edit-Goto-Special-Blanks.
Live Cell will now be A2. Enter text "=A1" BUT press Ctrl+ENTER (rather than just ENTER).
Select A1 then Ctrl+Shift+8 to select area.
Edit-Copy-Edit-Paste Special-Values.
Now sort and get rid of the numbers in Column B if you do not need them.

"Simple self-teach tutorial on VBA"
"Microsoft Excel 2002 Visual Basic for Applications Step by Step" by MS press is good. Started my learning with the '97 version of this book which was great.