[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

change number form rows to columns

I have attached an excel sheet
if you look at row2-  from column G to CH are serial numbers.
I transposed row 2 data on row 13 ( which created about 80 records)

I have about 400 records like this- is there anyway access can transpose the numbers?
thank you

SerialNo.xls
0
davetough
Asked:
davetough
  • 5
  • 5
1 Solution
 
SiddharthRoutCommented:
I can write a small Excel macro which can do that for you?

Sid
0
 
davetoughAuthor Commented:
a excel macro would be fine- if it can do that
0
 
SiddharthRoutCommented:
The output will be in Different Sheet. Would that be ok?

Sid
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
PatOBrienCommented:
If you're doing this just once or twice, you could Copy / Paste Special, selecting the "Transpose" option.

If you don't want to use a macro, you can also use the worksheet function TRANSPOSE as an array function (enter the formula by hitting Ctrl+Shift+Enter) which shows up with curly brackets { } around the formula.

0
 
davetoughAuthor Commented:
different sheet fine
I'll try what you are suggesting pat
0
 
SiddharthRoutCommented:
Ok. Please find a sample attached.

Please run the macro "Sample". The out will be generated in a new sheet called "output"

Sid

Code Used

Option Explicit

Sub Sample()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim i As Long, wslastrow As Long, ws1lastrow As Long
    Dim r As Long, R1 As Long, R2 As Long
    
    Set ws = Sheets("tblTdaz")
    Set ws1 = Sheets.Add
    ws1.Name = "Output"
    
    For i = 1 To 6
        With ws1
            .Cells(1, i) = ws.Cells(1, i)
        End With
    Next i
    ws1.Cells(1, 7) = "SN"
    
    wslastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws1lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For i = 2 To wslastrow
        ws.Range("A" & i & ":F" & i).Copy ws1.Range("A" & ws1lastrow)
        ws.Range("G" & i & ":CH" & i).Copy
        ws1.Range("G" & ws1lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        R1 = ws1.Range("F" & Rows.Count).End(xlUp).Row
        R2 = ws1.Range("G" & Rows.Count).End(xlUp).Row
        r = R2
        ws1.Range("A" & R1 & ":F" & R1).Copy ws1.Range("A" & R1 + 1 & ":F" & r)
        If R1 > R2 Then r = R1 Else r = R2
        ws1lastrow = r + 1
    Next
End Sub

Open in new window

SerialNo.xls
0
 
davetoughAuthor Commented:
thank you
0
 
davetoughAuthor Commented:
I forgot to ask-SiddharthRout-
if i use this macro on 40 records- will it work? or should I change code in macro any?
thank you
0
 
davetoughAuthor Commented:
I mean 400
0
 
SiddharthRoutCommented:
It will work for 40 records as well till the time they are arranged one below the other leke your 5 rows :)

Sid
0
 
SiddharthRoutCommented:
400 as well :)

Sid
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now