I have a dilemma. I am not even sure how to describe it....so I will show you what I am trying to do:
What I have now (thousands of columns like this)
| 424253 | 373211 | 412117 | 408338 |
| 1374 | 1374 | 1374 | 1374 |
| 1339 | 1339 | 1339 | 1339 |
want to make just two columns with thousands of rows like this (notice I also duplicate the first row in the first column (rather than leave it empty)
| 424253 | 1374 |
| 424253 | 1339 |
| 412117 | 1374 |
| 412117 | 1339 |
| 408338 | 1374 |
| 408338 | 1339 |
| 373211 | 1374 |
| 373211 | 1339 |
My question is...is there an easy way to do this in excel?
Hello Shawn,
Welcome to MOTH. If you haven't already, please take a look at this blog article, it might give you the answer you look for.
https://www.myonlinetraininghub.com/transpose-data-in-excel
Hi Shawn
You mentioned thousands of rows but did not mention how many columns.
If you don't have too many columns then I suggest adding some helper columns and using a few simple formulas and copy and paste value into two columns.
It is simple and will get the job done in just a few minutes.
Refer my attachment.
Hope this helps.
Sunny
Unfortunately I do have a Thousand Columns too...so this tactic wouldn't really work. I appreciate you trying to help out. I am going to look into more of the Transpose options that Anders Sehlstedt mentioned...maybe I can write a VBA script to help me out.
Sub Button1_Click()
Dim c As Range, rng As Range
Dim sh As Worksheet, ws As Worksheet
Dim LstRw As Long, rws As Long
Set sh = Sheets("Sheet1")
Set ws = Sheets("Sheet2")
With sh
Set rng = .Rows("1:1").SpecialCells(xlCellTypeConstants, 23)
For Each c In rng.Cells
LstRw = .Cells(.Rows.Count, c.Column).End(xlUp).Row
For rws = 2 To LstRw
ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1) = c
ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1) = .Cells(rws, c.Column)
Next rws
Next c
End With
End Sub
See attached
Note: Forgot to mention the results are on sheet2
TransposeSample
