Use excel transpose or similar


We have a super big excel with over 1,200 rows 255 columns (rows=modems, columns=ports).  We have been using excel transpose to just have 2 columns modems and ports (see below example).

Modem   Port-01 Port-02 Port-03
-------- ------ ------- --------
NetGear   4672   555    SMS
Arris     non     x     SMS

Open in new window

We want to convert the data into 2 columns as follows:

Modem	Ports
NetGear	4672
NetGear	555
NetGear	SMS
Arris	non
Arris	x
Arris	SMS

Open in new window

We have used excel transpose, but we have to do this one row by row.

Is there a way for using transpose or another  function so we can get the result we are  looking for?
(attached sample files of current and trying to accomplish)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
(oops! forgot to attachment)
Ejgil HedegaardCommented:
Try attached, press button to run.
rayluvsAuthor Commented:
Thanx! I thought that there was a way with transpose, but thank you, VBA is the way.

Prior closing the question,
- Can you give a brief on the statement "Set wsIn = ThisWorkbook.Worksheets("Sheet2")"
- Also, in the real excel we have over 200 columns, where do I increase the value to consider converting those columns also to rows?
Ejgil HedegaardCommented:
The statement assigns the sheet to a variable.
Doing so makes coding easier, because IntelliSense shows the options for a worksheet, so you don't have to remember, makes addressing the sheet easier, and avoids some errors.

Both columns and rows are counted, for the region with the cell A1.
A region is a range surrounded by empty cells.
So put your real data in A1 down and across, and it will work.

I forgot to turn off screen updating (speeds up processing), so here is the code with that.

Option Explicit

Sub ColumnsToRows()
    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim rwIn As Long, rwOut As Long, colIn As Integer
    Set wsIn = ThisWorkbook.Worksheets("Sheet2")
    Set wsOut = ThisWorkbook.Worksheets.Add
    Application.ScreenUpdating = False
    wsOut.Range("A1:B1") = Array("Modem", "Ports")
    rwOut = 1
    For rwIn = 2 To wsIn.Range("A1").CurrentRegion.Rows.Count
        For colIn = 2 To wsIn.Range("A1").CurrentRegion.Columns.Count
            rwOut = rwOut + 1
            wsOut.Cells(rwOut, 1) = wsIn.Cells(rwIn, 1)
            wsOut.Cells(rwOut, 2) = wsIn.Cells(rwIn, colIn)
        Next colIn
    Next rwIn
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.