Multiple columns into multiple rows

[houlu]

Hi All,

I have attached a sample excel file same like my actual file. First Sheet named "RawData" contains my raw data and the second sheet contains the data that looks like I need . This is a sample file. my actual data is till 350 Rows and till AN column. And my actual data starts from 9th row.

I need to use this with Excel 2003.

Please give a proper solution in this way so that if i work in Excel 2003, your solution will work. No issue, if i select my range , two times.


Thanks in advance.

Hope reply from your end.

With Regards.
Manoj

[CodeNinja]

Houlu,
I think something like this would work for you...

[VBA]
Sub test()
Dim iCol As Integer
Dim iRow As Integer
Dim iRow2 As Integer
Dim i As Integer
Dim rng As Range
Dim iTemp As Integer

'Header:
Set rng = Sheet1.Range("B11:B15")
For iCol = 3 To Sheet1.Range("IV9").End(xlToLeft).Column
i = (iCol - 2)
Sheet2.Cells(1, 4 + ((i - 1) * 6)) = Sheet1.Cells(9, iCol)
rng.Copy
Sheet2.Activate
Sheet2.Cells(2, 2 + ((i - 1) * 6)).Select

ActiveCell.PasteSpecial Transpose:=True
Next iCol

iRow2 = 2

'Data:
For iRow = 11 To Sheet1.Range("A65536").End(xlUp).Row
If Sheet1.Cells(iRow, 1) <> "" Then
iRow2 = iRow2 + 1
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1)
For iCol = 3 To Sheet1.Range("IV11").End(xlToLeft).Column
Set rng = Sheet1.Range(getColLtr(iCol) & iRow & ":" & _
getColLtr(iCol) & Sheet1.Range(getColLtr(iCol) & iRow).End(xlDown).Row)
rng.Copy
If rng.Cells.count < 10 Then
Sheet2.Activate
iTemp = (iCol - 2)
Sheet2.Range(getColLtr(iTemp + 1 + ((iTemp - 1) * 5)) & iRow2 & ":" & getColLtr((iTemp + 1) + rng.Cells.count + ((iTemp - 1) * 5)) & iRow2).Select
Selection.PasteSpecial Transpose:=True
End If
Next iCol
End If
Next iRow





End Sub


Private Function getColLtr(ByVal i As Integer) As String
' this function returns the string of a column number (i.e. A would return 1, B would return 2, and AA would return 27)
Dim j As Integer
j = 0
While i > 26
i = i - 26
j = j + 1
Wend
If j > 0 Then
getColLtr = Chr(j + 64) & Chr(i + 64) 'chr65 is "A", so numerically it goes up from there...
Else
getColLtr = Chr(i + 64)
End If


End Function

[/VBA]

[houlu]

Dear Mr. CodeNinja,
Thank You very much for your code and sorry to my late response.
Actually I was trying to modify your code to get desired result, but couldn't. Because my level in VBA is as a learner.

In the file I sent you , the code is working fine. But i want to write here some issues.............

1. If data starts from 11st row, code is working. But if data starts from any other row than 11, macro gives wrong results.
2. If i run the macro in any other workbook, macro gets the range of ThisWorkbook ( the workbook in which code is running.)

Actually we have many workbooks same as i sent you and in the workbooks data starting row can be variable. So if macro will ask to user...
1. input range ( as in your code - sheet1 's range that has to be transpose)
2.The row no from where output will start ( as in your code - sheet2).
3. if it is possible that we run macro in the active workbook without giving the name sheet1. And will get result in a new added worksheet in same workbook or any other workbook. That would be very good.

Hope to positive response as earlier,

Regards.
Houlu

Houlu,
I think something like this would work for you...

[vba]
Sub test()
Dim iCol As Integer
Dim iRow As Integer
Dim iRow2 As Integer
Dim i As Integer
Dim rng As Range
Dim iTemp As Integer

'Header:
Set rng = Sheet1.Range("B11:B15")
For iCol = 3 To Sheet1.Range("IV9").End(xlToLeft).Column
i = (iCol - 2)
Sheet2.Cells(1, 4 + ((i - 1) * 6)) = Sheet1.Cells(9, iCol)
rng.Copy
Sheet2.Activate
Sheet2.Cells(2, 2 + ((i - 1) * 6)).Select

ActiveCell.PasteSpecial Transpose:=True
Next iCol

iRow2 = 2

'Data:
For iRow = 11 To Sheet1.Range("A65536").End(xlUp).Row
If Sheet1.Cells(iRow, 1) <> "" Then
iRow2 = iRow2 + 1
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1)
For iCol = 3 To Sheet1.Range("IV11").End(xlToLeft).Column
Set rng = Sheet1.Range(getColLtr(iCol) & iRow & ":" & _
getColLtr(iCol) & Sheet1.Range(getColLtr(iCol) & iRow).End(xlDown).Row)
rng.Copy
If rng.Cells.count < 10 Then
Sheet2.Activate
iTemp = (iCol - 2)
Sheet2.Range(getColLtr(iTemp + 1 + ((iTemp - 1) * 5)) & iRow2 & ":" & getColLtr((iTemp + 1) + rng.Cells.count + ((iTemp - 1) * 5)) & iRow2).Select
Selection.PasteSpecial Transpose:=True
End If
Next iCol
End If
Next iRow





End Sub


Private Function getColLtr(ByVal i As Integer) As String
' this function returns the string of a column number (i.e. A would return 1, B would return 2, and AA would return 27)
Dim j As Integer
j = 0
While i > 26
i = i - 26
j = j + 1
Wend
If j > 0 Then
getColLtr = Chr(j + 64) & Chr(i + 64) 'chr65 is "A", so numerically it goes up from there...
Else
getColLtr = Chr(i + 64)
End If


End Function

[/vba]

[CodeNinja]

Houlu,
I am not sure if this is what you want, but I hope this helps...

[VBA]
Sub test()
Dim iCol As Integer
Dim iRow As Integer
Dim iRow2 As Integer
Dim i As Integer
Dim rng As Range
Dim iTemp As Integer
Dim iInput As Integer

'Header:
Set rng = Sheet1.Range("B11:B15")
For iCol = 3 To Sheet1.Range("IV9").End(xlToLeft).Column
i = (iCol - 2)
Sheet2.Cells(1, 4 + ((i - 1) * 6)) = Sheet1.Cells(9, iCol)
rng.Copy
Sheet2.Activate
Sheet2.Cells(2, 2 + ((i - 1) * 6)).Select

ActiveCell.PasteSpecial Transpose:=True
Next iCol

iRow2 = 2
iInput = InputBox("Enter the row where the data begins.")
'Data:
For iRow = iInput To Sheet1.Range("A65536").End(xlUp).Row
If Sheet1.Cells(iRow, 1) <> "" Then
iRow2 = iRow2 + 1
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1)
For iCol = 3 To Sheet1.Range("IV11").End(xlToLeft).Column
Set rng = Sheet1.Range(getColLtr(iCol) & iRow & ":" & _
getColLtr(iCol) & Sheet1.Range(getColLtr(iCol) & iRow).End(xlDown).Row)
rng.Copy
If rng.Cells.Count < 10 Then
Sheet2.Activate
iTemp = (iCol - 2)
Sheet2.Range(getColLtr(iTemp + 1 + ((iTemp - 1) * 5)) & iRow2 & ":" & getColLtr((iTemp + 1) + rng.Cells.Count + ((iTemp - 1) * 5)) & iRow2).Select
Selection.PasteSpecial Transpose:=True
End If
Next iCol
End If
Next iRow





End Sub


Private Function getColLtr(ByVal i As Integer) As String
' this function returns the string of a column number (i.e. A would return 1, B would return 2, and AA would return 27)
Dim j As Integer
j = 0
While i > 26
i = i - 26
j = j + 1
Wend
If j > 0 Then
getColLtr = Chr(j + 64) & Chr(i + 64) 'chr65 is "A", so numerically it goes up from there...
Else
getColLtr = Chr(i + 64)
End If


End Function



[/VBA]

[houlu]

Dear CodeNinja,

The provided code is not giving expected result.

I only want the following....
1. first a input box will prompt to user to select the range that should be transpose.
2. In that input box , user will type a range. He can select the range whatever he want e.g. in my attached sample file, .Range("C11:H18") or all the data range such as [B11:H34] or Range("1:15")
3. A second input box will prompt to user to select the row no. This will the row no. of output worksheet. e.g. if user select 12 , The conversion of Range("C11:H18") OR Range("B11:H34") etc. will start from the row no. 12 of sheet2 ( as per your code)

4. Percentage should be ignored. Means it should not be on result sheet.

Thanks Again,

Regards.
Houlu

Houlu,
I am not sure if this is what you want, but I hope this helps...

[vba]
Sub test()
Dim iCol As Integer
Dim iRow As Integer
Dim iRow2 As Integer
Dim i As Integer
Dim rng As Range
Dim iTemp As Integer
Dim iInput As Integer

'Header:
Set rng = Sheet1.Range("B11:B15")
For iCol = 3 To Sheet1.Range("IV9").End(xlToLeft).Column
i = (iCol - 2)
Sheet2.Cells(1, 4 + ((i - 1) * 6)) = Sheet1.Cells(9, iCol)
rng.Copy
Sheet2.Activate
Sheet2.Cells(2, 2 + ((i - 1) * 6)).Select

ActiveCell.PasteSpecial Transpose:=True
Next iCol

iRow2 = 2
iInput = InputBox("Enter the row where the data begins.")
'Data:
For iRow = iInput To Sheet1.Range("A65536").End(xlUp).Row
If Sheet1.Cells(iRow, 1) <> "" Then
iRow2 = iRow2 + 1
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1)
For iCol = 3 To Sheet1.Range("IV11").End(xlToLeft).Column
Set rng = Sheet1.Range(getColLtr(iCol) & iRow & ":" & _
getColLtr(iCol) & Sheet1.Range(getColLtr(iCol) & iRow).End(xlDown).Row)
rng.Copy
If rng.Cells.Count < 10 Then
Sheet2.Activate
iTemp = (iCol - 2)
Sheet2.Range(getColLtr(iTemp + 1 + ((iTemp - 1) * 5)) & iRow2 & ":" & getColLtr((iTemp + 1) + rng.Cells.Count + ((iTemp - 1) * 5)) & iRow2).Select
Selection.PasteSpecial Transpose:=True
End If
Next iCol
End If
Next iRow





End Sub


Private Function getColLtr(ByVal i As Integer) As String
' this function returns the string of a column number (i.e. A would return 1, B would return 2, and AA would return 27)
Dim j As Integer
j = 0
While i > 26
i = i - 26
j = j + 1
Wend
If j > 0 Then
getColLtr = Chr(j + 64) & Chr(i + 64) 'chr65 is "A", so numerically it goes up from there...
Else
getColLtr = Chr(i + 64)
End If


End Function



[/vba]

[houlu]

Someone leave me a reply.
my explanation / time consuming

Rgds.
Manoj

[stanleydgrom]

CodeNinja,

[vba]

Private Function getColLtr(ByVal i As Integer) As String
' this function returns the string of a column number (i.e. A would return 1, B would return 2, and AA would return 27)
Dim j As Integer
j = 0
While i > 26
i = i - 26
j = j + 1
Wend
If j > 0 Then
getColLtr = Chr(j + 64) & Chr(i + 64) 'chr65 is "A", so numerically it goes up from there...
Else
getColLtr = Chr(i + 64)
End If
End Function

[/vba]



Here are some alternative methods:

[vba]

Dim strColName As String
Dim lngLastCol As Long

' 1 = Row 1
lngLastCol = Cells(1, Columns.Count).End(xlToLeft).Column


'or

lngLastCol = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column


strColName = Replace(Cells(1, lngLastCol).Address(0, 0), 1, "")
strColName = Split(Columns(lngLastCol).Address, "$")(2)
strColName = Split(Cells(1, lngLastCol).Address, "$")(1)

[/vba]


Have a great day,
Stan

[CodeNinja]

Stanlydgrom,
Thanks I will keep those in mind for future.

Houlu,
Kinda busy now, will try to get to this if I can today... If anyone else wants to provide solution, be my guest.

[houlu]

Dear CodeNiza,
Thank u very much. It is enough to me. Now I look like, forum and its members both are very good. Have a good day.

With warm regards
Houlu

[CodeNinja]

Houlu,
Questions are marked in RED Below:
You wrote:
1. first a input box will prompt to user to select the range that should be transpose.
2. In that input box , user will type a range. He can select the range whatever he want e.g. in my attached sample file, .Range("C11:H18") or all the data range such as [B11:H34] or Range("1:15")

The range that is selected here, does it refer to the RawData sheet or the output needed/Result sheet?

3. A second input box will prompt to user to select the row no. This will the row no. of output worksheet. e.g. if user select 12 , The conversion of Range("C11:H18") OR Range("B11:H34") etc. will start from the row no. 12 of sheet2 ( as per your code)

This refers to the output needed sheet right?, so if someone selected 5, output needed/Result sheet (including header) would start at 5?

4. Percentage should be ignored. Means it should not be on result sheet.

This is not what you show on your sample data (see columns M-Q...) Do you want this to ignore all % data?

[houlu]

Dear CodeNinza,
1,2. Yes, the selected range refers to Raw Data sheet
3. Header (Years) should be same as those are. It will be good if all are in fist row. And Header (Quarters - q1,q2,q3,q4,fy) shoud be one row below years. And Headers(line items - Revenue, item1 etc.) should be on the same row of output needed sheet/ result sheet
4. I thought - to ignore percentage would be critical. Although it is a part of requirement.
Thank you,
Houlu

[CodeNinja]

Houlu,
Hopefully this is what you want.... It asks for 3 inputs:
1- the row with the year (this is necessary) from RawData sheet. This input box will only allow a number.
2- the row you wish to start data fill on for Output Needed/Results sheet (I believe you asked for this) This inputbox will only allow a number.
3- the range you wish to copy. You will actually be able to use the cursor and select the range on the sheet for this as well as type in a legit range. This will also not allow illegal ranges. (You asked for this as well)

Hope it solves your problem...


[VBA]
Sub test()
Dim iCol As Integer
Dim iRow As Integer
Dim iRow2 As Integer
Dim i As Integer
Dim rng As Range
Dim iTemp As Integer
Dim iInput As Integer
Dim rngTemp As Range
Dim iStart As Integer
Dim iHeader As Integer

iHeader = Application.InputBox(prompt:="Enter the row containing the years for the header.", Title:= _
"Enter year row.", Type:=1)

iStart = Application.InputBox(prompt:="Enter the row you wish to begin displaying the results on.", _
Title:="Enter results beginning row", Type:=1)

Set rngTemp = Application.InputBox(prompt:="Select the range you wish to copy.", Title:= _
"Select Range", Type:=8)


'Header:
Set rng = Sheet1.Range("B11:B15")
For iCol = rngTemp.Cells(1).Column To rngTemp.Cells(rngTemp.Cells.Count).Column Step 2
i = (iCol - 1) / 2
Sheet2.Cells(iStart, 4 + ((i - 1) * 6)) = Sheet1.Cells(iHeader, iCol)
rng.Copy
Sheet2.Activate
Sheet2.Cells(iStart + 1, 2 + ((i - 1) * 6)).Select

ActiveCell.PasteSpecial Transpose:=True
Next iCol

iRow2 = iStart + 1
'Data:
For iRow = rngTemp.Cells(1).Row To rngTemp.Cells(rngTemp.Cells.Count).Row
If Sheet1.Cells(iRow, 1) <> "" Then
iRow2 = iRow2 + 1
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1)
For iCol = rngTemp.Cells(1).Column To rngTemp.Cells(rngTemp.Cells.Count).Column Step 2
Set rng = Sheet1.Range(getColLtr(iCol) & iRow & ":" & _
getColLtr(iCol) & Sheet1.Range(getColLtr(iCol) & iRow).End(xlDown).Row)
rng.Copy
If rng.Cells.Count < 10 Then
Sheet2.Activate
iTemp = (iCol - 1) / 2
Sheet2.Range(getColLtr(iTemp + 1 + ((iTemp - 1) * 5)) & iRow2 & ":" & getColLtr((iTemp + 1) + rng.Cells.Count + ((iTemp - 1) * 5)) & iRow2).Select
Selection.PasteSpecial Transpose:=True
End If
Next iCol
End If
Next iRow





End Sub


Private Function getColLtr(ByVal i As Integer) As String

getColLtr = Split(Cells(1, i).Address, "$")(1)

End Function



[/VBA]

[snb]

crosspost

http://www.excelfox.com/forum/f2/tra...-442/#post1699

[CodeNinja]

Houlu,
Bad form. Cross posting is against forum rules

per http://www.vbaexpress.com/forum/showthread.php?t=18537

Cross Posting
Definition of cross-posting: Cross-posting is where a poster posts a question in a Help forum on this board (VBAX) and the same question in a Help forum on another board.

Rules: Cross-posting is permitted with the proviso that you make it clear that you have cross-posted and a link to the cross-post must be provided. (The reason for this being that if a link is not included, volunteer helpers may very well waste their time answering questions that have already been resolved on another board).

You must also allow a reasonable period of time for someone to reply to your question before you cross-post, that is, even when links are provided, posting your question more or less simultaneously on more than one board is not acceptable.

Penalties: Cross-posters that do not include a link to their cross-post or allow reasonable time for a response before cross-posting may find their thread has been deleted and posters that persist in this behaviour may find themselves banned.

Thank you

And all this _AFTER_ I provided latest solution...

Very sad indeed.

[houlu]

snb,
You did something good. We should always follow the rules.
But totally by mistake, I did something wrong. And I am feeling very bad right now.
Snb, my query is not solved yet.
Do U have any idea about VBA. Please Try to solve this ?

Dear CodeNinza,

Sorry for the double post without linking. But actually I didn't read the complete rules. And one more thing , I have never been a member in any forum except these last 3 or 4 days.

I appreciate your efforts. Percentage issue is solved.

but limitations are as earlier. Ninza, I can understand code, but a simple user cann't .
He can also select the entire range.
1. if data is big - Transpose Error ("select a single a cell.....
2.I can run this macro only with the first created sheet1 and Sheet2
3. I can not use this on any other sheet. If i do macro always takes the range of sheet1 and sheet2
or I will have to create a blank workbook or add sheets , every time i want to transpose a range.
4. Actually user can select any range, So that is a big issue.

Anyway,

Thank U very much,

Nice to meet with you.

Have a great day, and sorry for my big mistake.

Regards.
Houlu

Houlu,
Bad form. Cross posting is against forum rules

per http://www.vbaexpress.com/forum/showthread.php?t=18537

Cross Posting
Definition of cross-posting: Cross-posting is where a poster posts a question in a Help forum on this board (VBAX) and the same question in a Help forum on another board.

Rules: Cross-posting is permitted with the proviso that you make it clear that you have cross-posted and a link to the cross-post must be provided. (The reason for this being that if a link is not included, volunteer helpers may very well waste their time answering questions that have already been resolved on another board).

You must also allow a reasonable period of time for someone to reply to your question before you cross-post, that is, even when links are provided, posting your question more or less simultaneously on more than one board is not acceptable.

Penalties: Cross-posters that do not include a link to their cross-post or allow reasonable time for a response before cross-posting may find their thread has been deleted and posters that persist in this behaviour may find themselves banned.

Thank you

And all this _AFTER_ I provided latest solution...

Very sad indeed.