I've search for a week now and can't seem to find an answer.

I have a spreadsheet that contains about 150 columns with row 1 being an
account number and rows 2..n contain products purchased. Example:
10 20
apples cans
oranges boxes
grapes

Some clients may have only 1 product while another client may have 300.

How can I transpose the data into rows and maintain the account number with
the item? Example:
10 apples
10 oranges
10 grapes
20 cans
20 boxes

Any help would be appreciated.

Thanks,
Allan

This seems to work

Sub makelist()
Worksheets("Sheet1").Activate
LastAccount = Range("A1", Range("A1").End(xlToRight)).Count
RowCount = 1
For j = 1 To LastAccount
ThisAccount = Cells(1, j)
MyRow = 2
Do
If Cells(MyRow, j) = "" Then Exit Do
' MsgBox Cells(MyRow, j)
Worksheets("Sheet2").Cells(RowCount, 1) = ThisAccount
Worksheets("Sheet2").Cells(RowCount, 2) = Cells(MyRow, j)
MyRow = MyRow + 1
RowCount = RowCount + 1
Loop
Next j
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Allan" wrote in message
...
I've search for a week now and can't seem to find an answer.

I have a spreadsheet that contains about 150 columns with row 1 being an
account number and rows 2..n contain products purchased. Example:
10 20
apples cans
oranges boxes
grapes

Some clients may have only 1 product while another client may have 300.

How can I transpose the data into rows and maintain the account number
with
the item? Example:
10 apples
10 oranges
10 grapes
20 cans
20 boxes

Any help would be appreciated.

Thanks,
Allan

Try code like

Sub AA()
Dim RR As Range
Dim RC As Range
Dim Dest As Range
Dim H As String

' Set RR to the first cell of original data
Set RR = Worksheets("Sheet1").Range("A1")
' Set Dest to the first cell of the summarized data
Set Dest = Worksheets("Sheet2").Range("A1")

Do Until RR.Value = vbNullString
H = RR.Value
Set RC = RR(2, 1)
Do Until RC.Value = vbNullString
Dest(1, 1).Value = H
Dest(1, 2).Value = RC.Value
Set RC = RC(2, 1)
Set Dest = Dest(2, 1)
Loop
Set RR = RR(1, 2)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 27 Mar 2009 06:25:03 -0700, Allan
wrote:

I've search for a week now and can't seem to find an answer.

I have a spreadsheet that contains about 150 columns with row 1 being an
account number and rows 2..n contain products purchased. Example:
10 20
apples cans
oranges boxes
grapes

Some clients may have only 1 product while another client may have 300.

How can I transpose the data into rows and maintain the account number with
the item? Example:
10 apples
10 oranges
10 grapes
20 cans
20 boxes

Any help would be appreciated.

Thanks,
Allan

Bernard/Chip;

Thank you both so much! I tried both solutions and both worked perfectly!

Thanks again!
Allan

"Allan" wrote:

I've search for a week now and can't seem to find an answer.

I have a spreadsheet that contains about 150 columns with row 1 being an
account number and rows 2..n contain products purchased. Example:
10 20
apples cans
oranges boxes
grapes

Some clients may have only 1 product while another client may have 300.

How can I transpose the data into rows and maintain the account number with
the item? Example:
10 apples
10 oranges
10 grapes
20 cans
20 boxes

Any help would be appreciated.

Thanks,
Allan