Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposition impossibility?
When I import my inventory records from the accounting package this is what I have:- Code Size 1 3 1 6 1 7 2 2 2 3 2 4 2 6 3 4 4 1 4 3 You get the picture - twelve pages of codes with different sizes. This is what I need:- Code Sizes 1 3 6 7 2 2 3 4 6 3 4 4 1 3 With a random number of lines being reported for the sizes for the product codes, I can't figure out a way to run a copypaste specialtranspose macro because doing it manually sucks. It would be best if the sizes went into different columns so I could run further analysis but any ideas gratefully accepted. -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096 View this thread: http://www.excelforum.com/showthread...hreadid=537553 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposition impossibility?
My layout lost its formatting when I submitted it - the data comes into Excel in two columns, code and size. -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096 View this thread: http://www.excelforum.com/showthread...hreadid=537553 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposition impossibility?
With a random number of lines being reported for the sizes for the
product codes, I can't figure out a way to run a copypaste specialtranspose macro because doing it manually sucks. It would be best if the sizes went into different columns so I could run further analysis but any ideas gratefully accepted. Rob, you can run this code Sub transform() Set b = Worksheets("Sheet1") '< change name if needed iRow = Application.CountA(Columns(1)) xRow = 1 xCol = 5 For i = 2 To iRow If Cells(i, 1) < Cells(i - 1, 1) Then xRow = xRow + 1: xCol = 5 Cells(xRow, 4) = Cells(i, 1) End If Cells(xRow, xCol) = Cells(i, 2) xCol = xCol + 1 Next End Sub This is if your list is in column A:B and starts from the top. The result comes in column D: ... -- Stefan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposition impossibility?
" Rob,
you can run this code Sub transform() Set b = Worksheets("Sheet1") '< change name if needed iRow = Application.CountA(Columns(1)) xRow = 1 xCol = 5 For i = 2 To iRow If Cells(i, 1) < Cells(i - 1, 1) Then xRow = xRow + 1: xCol = 5 Cells(xRow, 4) = Cells(i, 1) End If Cells(xRow, xCol) = Cells(i, 2) xCol = xCol + 1 Next End Sub This is if your list is in column A:B and starts from the top. The result comes in column D: ... -- Stefan Sorry, first line (set b= ...) isn't necessary if the sheet is visible on your screen; otherwise you must add b. before every Cells in the code. Cells(... = b.Cells(... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transposition impossibility?
Many thanks Stephan. It works brilliantly... -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096 View this thread: http://www.excelforum.com/showthread...hreadid=537553 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Impossibility? | Excel Worksheet Functions | |||
Transposition | Excel Worksheet Functions |