Solved

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

Posted on 2016-11-14
8
53 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article will help you understand what HashTables are and how to use them in PowerShell.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

825 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