transposing data question

A

Allan

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
 
B

Bernard Liengme

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
 
C

Chip Pearson

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

Allan

Bernard/Chip;

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

Thanks again!
Allan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top