Solved

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

Posted on 2016-11-14
8
48 Views
Last Modified: 2016-12-01
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
Comment
Question by:100questions
  • 5
  • 3
8 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 41886849
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
 

Author Comment

by:100questions
ID: 41886863
Thanks.  
How will I trigger this to run?  Externally?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41886987
Instantiate an Excel.automation object, open the workbook, and then use its RUN() method to invoke the routine.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41887047
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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 45

Expert Comment

by:aikimark
ID: 41887050
@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
 

Author Comment

by:100questions
ID: 41891957
@aikimark.
Thanks, what are the steps to add this scrip in Excel?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41891969
1. create a module in the VBProject environment
2. paste the code/routine into the module
0
 

Author Closing Comment

by:100questions
ID: 41909146
Thank you, this works great.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now