Convert Cols into Rows

[sukumar.vb]

Hi all,

I am looking for a VBA Code to convert my 30 data in columns to fit into a single row. There is a English term for this. I can't recall that term. But, thanz to you, if you can tell me VBA code to convert data from Cols to fit into rows. May be you can use for next loop or Do while loop. Anything working. I am just looking for an idea.

Is there any VBA code for following:-

Problem:

Transposing the values in List1 (cells A2:A5) into a row, and the values in List2 (cells B10:F10) into a column.

Solution 1: To transpose from a Column into a Row.

To transpose the values in Column A (List1) into a Row:
Using the INDEX and COLUMN functions, enter the following formula in cell C2, and then copy it across the next 3 cells in the row:
=INDEX($A$2:$A$5,COLUMN()-COLUMN($C$2)+1)
Alternative solution:
Select cells C2:F2 and enter the TRANSPOSE function as shown in the following Array formula:
{=TRANSPOSE(A2:A5)}

Solution 2: To transpose from a Row into a Column.

To transpose the values in Row 10 (List2) into a Column:
Using the INDEX and ROW functions, enter the following formula in cell H10, and then copy it down to the next 4 cells in the column:
=INDEX($B$10:$F$10,ROW()-ROW($H$10)+1)
Alternative solution:
Select cells H10:H14 and enter the TRANSPOSE function as shown in the following Array formula:
{=TRANSPOSE(B10:F10)}

Thanz
Sukumar

[JKwan]

Perhaps this will do it for you
http://www.cpearson.com/excel/ColumnToTable.aspx
OR
if your data does not have a pattern
http://www.cpearson.com/excel/Variab...mnToTable.aspx

[sukumar.vb]

Hi,

I greatly acknowledge your praiseworthy reply. I liked it. I am looking for a VBA code which can help me in following Way:- I have table in which one col contains Basket Name and second col contains fruits name. Basket Name is same throughout the col, but fruits name change in every col. So, Now, I need a code which can put Basket name into one cell and all other fruits would come into adjacent cells (in one row only). Code should also verify that if basket name changes then it would write data is second row as :- Basket2, Fruit33, fruit34, fruit35, etc.

Anyway thanz buddy.

Sukumar

[sukumar.vb]

Hi Jwan,



I request you to download this Attachment 188 attached file. Here one sheet shows Problem Statement and other sheet shows expected solution. Now I need a VBA code which can convert data from "Problem" Sheet into "Solution" sheet.

I have Anti-Virus installed on my PC with latest updates. So, plz do not worry about file. Its just 14 Kbs.

Thanz,
Sukumar

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim LastRow As Long
Dim LastCol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
.Cells(i, "C").Resize(, LastCol - 2).Copy .Cells(i - 1, "D")
.Rows(i).Delete

ElseIf .Cells(i, TEST_COLUMN).Value = "" Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[sukumar.vb]

Hi,

Thanz 4 your help.

I request you to please download this file Attachment 228 and another file which I have posted above. It will help you to understand my requirement.

[Bob Phillips]

Not useful! Well, I am sorry about that.

[Krishna Kumar]

Hi,

[vba]Sub kTest()
Dim a, i As Long, n As Long, w(), r(), dic As Object, lCol As Long

a = Range("b2:c" & Range("b" & Rows.Count).End(xlUp).Row)
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
ReDim w(1 To UBound(a, 1), 1 To Columns.Count)

For i = 1 To UBound(a, 1)
If Not IsEmpty(a(i, 1)) Then
If Not dic.exists(a(i, 1)) Then
n = n + 1
w(n, 1) = a(i, 1): w(n, 2) = a(i, 2)
dic.Add a(i, 1), Array(n, 2)
Else
r = dic.Item(a(i, 1)): r(1) = r(1) + 1
w(r(0), r(1)) = a(i, 2)
lCol = Application.Max(lCol, r(1))
dic.Item(a(i, 1)) = r
End If
End If
Next
With Range("e2")
.Resize(n, lCol).Value = w
End With
Erase a: Set dic = Nothing
End Sub[/vba]

HTH

[sukumar.vb]

it did not work...

[mdmackillop]

I find both solutions work with your posted sample.