Moving Columns to Rows

[David Patterson]

Is there any easy way I can achieve the following without a very laborious
route of manually copying and pasting loads of data? I have a large
spreadsheet of users and their software applications. It has been prepared
with the first column being used for the user's name and subsequent columns
each representing an application name. There is one row per user with a
figure 1 in each column that represents an application the user has.

I want to change that so that every user and application is on a row of its
own. Thus if a user has 6 applications there will be 6 rows. The first
part below illustrates what I have and the second part illustrates what I
would like. I need to change it so I can import into Access and compare
against tables that have been formatted as per what I want to achieve in
Excel.

Name App 1 App2 App 3 App 4 App 5
AN Other 1 1 1 1
ZZ Other 1 1 1



Name App
AN Other App 1
AN Other App 3
AN Other App 4
AN Other App 5
ZZ Other App 1
ZZ Other App 2
ZZ Other App 5


Any easy way, please? Your help will be greatly appreciated.

Thanks,

David.

 

[David Patterson]

I should have said I am using Excel 97.

David.

 

[dderolph]

The Transpose function can convert columns to rows, or vice versa. Fo
instructions on using Transpose, open Help, select the Index tab, an
enter "transposing rows and columns" in the box.

Edit: correction, the Transpose function is an array formula. Th
procedure I'm really referring to from Excel's Help is a Copy and Past
procedure. First, copy the rows or columns you want converted, th
position your cell pointer at the beginning of your paste area and use
Paste Special, putting a check in the box for Transpose, to convert you
data

 

[Dave Peterson]

First, you misspelled your name! <vbg>.

Second, the fields got a little crushed in your post. But you have one column
for name and one column for different applications. You want to keep the name
of the application (in row 1) if the value in that cell is not empty???

If yes:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim iRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add
newWks.Range("a1").Resize(1, 2).Value _
= Array("Name", "App")

oRow = 1
With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 2
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iRow = FirstRow To LastRow
For iCol = FirstCol To LastCol
If .Cells(iRow, iCol).Value <> "" Then
oRow = oRow + 1
newWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
newWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value
End If
Next iCol
Next iRow
End With

End Sub

 

[David Patterson]

Thanks, but that really isn't going to help in creating all the additional
rows I need unless I do it a column at a time with lots of cutting and
pasting to create all the additional rows that I need. Remember, at the
moment, I have one row per user and out of a total of about 100 columns that
user could have, say, thirty applications marked. Each of those thirty
needs to go in a new row so that one user will now have thirty rows.

David.

 

[Debra Dalgleish]

To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

 

[David Patterson]

Debra,

Thanks very much. John Walkenbach's technique was exactly what I needed and
let me easily sail through 14 spreadsheets, all with 100+ columns.

David.

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that it worked.