Moving Columns to Rows

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.

I should have said I am using Excel 97.

David.


"David Patterson" wrote in message
...
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.

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

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


---
Message posted from http://www.ExcelForum.com/

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 wrote:

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.

--

Dave Peterson

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.

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

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


---
Message posted from http://www.ExcelForum.com/

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" wrote in message
...
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 wrote:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

David Patterson wrote:
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" wrote in message
...

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 wrote:

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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html