Share this content

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

One for the Excel experts - I need to switch...

Didn't find your answer?

I'm importing a big file and need to switch the data from rows into columns. The import file is .txt with fields separated by blanks like this:

100 London Paris Geneva Moscow Turin
101 Washington Caracas Brussels Dublin

And I need to transpose it into a format like this in Excel.

100 London
100 Paris
100 Geneva
100 Moscow
100 Turin
101 Washington
101 Caracas
101 Brussels
101 Dublin

Paste Special - Transpose doesn't quite do it. Any suggestions much appreciated as it I am spending an age doing this manually at the moment. David Carter

Replies (16)

Please login or register to join the discussion.

By Richard Willis
22nd Jun 2005 16:43

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!!!

Thanks (0)
By ACDWebb
23rd Jun 2005 00:16

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

Good luck and persevere.

Thanks (0)
By listerramjet
21st Mar 2005 14:05

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.

Thanks (0)
By Richard Willis
21st Mar 2005 13:28

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.

Thanks (0)
By Richard Willis
17th Mar 2005 13:46

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

Thanks (0)
By listerramjet
18th Mar 2005 09:35

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

Thanks (0)
By listerramjet
17th Mar 2005 16:26

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.

Thanks (0)
By AnonymousUser
10th Mar 2005 12:20

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()

Thanks (0)
By Richard Willis
10th Mar 2005 13:47

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!

Thanks (0)
By listerramjet
10th Mar 2005 11:36

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


Sub splititit()

Count = 0
Bigcount = 0
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
ActiveCell.Offset(1, 0).Activate
Bigcount = Bigcount + Count
Count = 0

End Sub


Thanks (0)
By ACDWebb
10th Mar 2005 11:58

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

Thanks (0)
By David Carter
11th Mar 2005 15:43

Like Alastair, I thought it would have to be VB this time. But we have three code free solutions.
There are 12 columns max per line, so I tried Richard's way last night on a 750 row import file and it took just 15 minutes. This will save me hours. Thanks everyone.

Thanks (0)
By AnonymousUser
11th Mar 2005 12:48

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.
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.

Thanks (0)
By David Carter
17th Mar 2005 21:24

It's always easy when you know how!
"Not that difficult". Are you volunteering to provide us with a simple self-teach tutorial on VBA, Alastair? I'd be first in the queue.

Thanks (0)
By David Carter
18th Mar 2005 13:04

Step by Step
Stephen, I'd forgotten all about the Microsoft Step by Step series. I'm on to them right now - free press reviewer's copy, of course.
Alastair, I shall make a point of working my way through your VB solution to this problem.

Thanks (0)
By AnonymousUser
18th Mar 2005 11:15

"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.

Thanks (0)
Share this content

Related posts