Solved

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

Posted on 2016-11-14
8
58 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

739 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