[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • 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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
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

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

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