[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How can I sort the data shown in Sheet 1 and copy it to Sheet 2?

I would like to sort through the Data in Sheet 1, and produce the output shown in Sheet 2.
I will add much more data to Sheet 1, perhaps over 600 rows and perhaps it may grow.
Can someone help me either to use Excel, or perhaps a script to obtain the desired output?
Thanks kindly.

SortingofData.xlsxSortingofData.xlsx
0
100questions
Asked:
100questions
  • 5
  • 3
1 Solution
 
aikimarkCommented:
Please test this VBA code:
Sub Q_28982950()
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim rng As Range
    Dim rngSrc As Range
    Dim rngTgt As Range
    Set wksSrc = Worksheets("Sheet1")
    Set wksTgt = Worksheets("Sheet3")
    Set rng = wksSrc.Range("C6")
    Set rngTgt = wksTgt.Range("B2")
    Application.ScreenUpdating = False
    wksTgt.Range("A1:E1").Value = Array("Year", "Product Name", "Pack Size", "UPC Code", "Costs")
    Do Until Len(rng.Value) = 0
        Set rngSrc = rng.End(xlDown).Offset(1, -1)
        Set rngSrc = wksSrc.Range(rngSrc, rngSrc.Offset(0, 3).End(xlDown))
        rngSrc.Copy rngTgt
        wksTgt.Range(rngTgt.Offset(0, -1), rngTgt.End(xlDown).Offset(0, -1)).Value = rng.Value
        Set rngTgt = rngTgt.End(xlDown).Offset(1)
        Set rng = rng.End(xlToRight)
    Loop
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
100questionsAuthor Commented:
Thanks.  
How will I trigger this to run?  Externally?
0
 
aikimarkCommented:
Instantiate an Excel.automation object, open the workbook, and then use its RUN() method to invoke the routine.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
aikimarkCommented:
Please note that I am populating the cells in a "Sheet3" worksheet (for testing purposes).  You will need to change that to "Sheet2" for your production purposes.
0
 
aikimarkCommented:
@100questions

You could also tweak this code and run it entirely in a VBScript environment.  You will need to remove the "As datatype" keywords from the Dim statements and add constant definitions for the xlDown and xlToRight values.
0
 
100questionsAuthor Commented:
@aikimark.
Thanks, what are the steps to add this scrip in Excel?
0
 
aikimarkCommented:
1. create a module in the VBProject environment
2. paste the code/routine into the module
0
 
100questionsAuthor Commented:
Thank you, this works great.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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