Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Manipulating excel data to new format

Posted on 2013-11-17
7
Medium Priority
?
487 Views
Last Modified: 2013-12-01
Hello experts

I need some help with the following.
I need to find out a way to change an existing data structure into a new one as automated as possible.

I have several excel files with each of them thousands of lines, so i really can't do it manually.

The main change is that instead of one row per employee, the new structure needs one row per company, with the employee's added to the end of the company row.

To be more clear i will add an example excel file with OLD DATA & NEW DATA sheets.
I manually copied the data so its clear as to how it should be transformed.

Before i go ahead and loose hours on some VBA code that is never going to work i wanted to ask the experts first.
Because this is probably a 5 min job for any veteran excel vba user....
example.xls
0
Comment
Question by:Th0R
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39656044
Hi Th0R,

Why are you not retaining the "Date" of each subsequent name with the same "DOCNR"/"TEAMREP"/"PHONE/"PREFIX"/"COMPANYNAME" combination?

You only seem to be retaining the day & month values, not the year of any name beyond the first.

Was that intentional, or an oversight?

The main change is that instead of one row per employee, the new structure needs one row per company, with the employee's added to the end of the company row.

Also, please confirm that "per company" means the same value in column [E] of the [OLD DATA] worksheet, or whether "DOCNR", "TEAMREP", "PHONE", &/or "PREFIX" also have to match (columns [A] to [E] inclusive).

Finally, will each of the workbooks where you require to transform the data be opened individually, & the code executed manually on a one-by-one basis, or are you looking to specify a folder that contains a collection of appropriate workbooks & for each to be converted/transformed in an automatic manner (without further user intervention).

Either way, not really "a 5 min job" :)

BFN,

fp.
0
 

Author Comment

by:Th0R
ID: 39656093
Hello

Why are you not retaining the "Date" of each subsequent name with the same

This is intentionally, NEW DATA > F G H have the lastname, firstname and full date of the first person.
that same person is then again repeated in I J K like the rest of the employees that follow.

Also, please confirm that "per company" means the same value in column [E] of the [OLD    DATA] worksheet, or whether "DOCNR", "TEAMREP", "PHONE", &/or "PREFIX" also have to  match (columns [A] to [E] inclusive).

The OLD DATA > A and E should be used to match. There is no matching relation between the rest ( B,C,D,...), even using A on its own without E should be sufficient
A and E are unique to that one company

Finally, will each of the workbooks where you require to transform the data be opened individually, & the code executed manually on a one-by-one basis, or are you looking to specify a folder that contains a collection of appropriate workbooks & for each to be converted/transformed in an automatic manner (without further user intervention).

To open the files, and run it manually would be more than enogh.

thnx
0
 
LVL 46

Expert Comment

by:aikimark
ID: 39656416
Why are you dropping the last name in the transformation process?
0
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 

Author Comment

by:Th0R
ID: 39656563
Because its not used in the new system
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 39659806
This code transforms the data in your Old Data worksheet, placing the results into the Sheet3 worksheet.

Option Explicit

Public Sub Q_28296534()
    Dim rngSrc As Range, wksSrc As Worksheet
    Dim rngTgt As Range, wksTgt As Worksheet
    Dim lngLoop As Long
    
    Set wksSrc = Worksheets("Old Data")
    Set wksTgt = Worksheets("Sheet3")
    Set rngTgt = wksTgt.Range("A1")
    Set rngSrc = wksSrc.Range("A1")
    
    'copy standard headers
    Set rngSrc = wksSrc.Range(rngSrc, rngSrc.End(xlToRight))
    rngSrc.Copy rngTgt
    'position for data copy
    Set rngTgt = rngTgt.Offset(1)
    Set rngSrc = rngSrc.End(xlDown)
    Do
        Set rngSrc = wksSrc.Range(rngSrc, rngSrc.End(xlToRight))
        rngSrc.Copy rngTgt
        Set rngSrc = wksSrc.Range(rngSrc.Cells(1, 7), rngSrc.Cells(1, 8).End(xlDown))
        Set rngTgt = rngTgt.End(xlToRight).Offset(0, 1)
        wksTgt.Range(rngTgt, rngTgt.Offset(1, rngSrc.Rows.Count - 1)).Value = WorksheetFunction.Transpose(rngSrc.Value)
        Do
            wksTgt.Range(rngTgt.Offset(0, 1), rngTgt.Offset(1, 2)).Columns.Insert xlToRight
            Set rngTgt = rngTgt.End(xlToRight)
            If Len(rngTgt.Value) = 0 Then
                Set rngTgt = rngTgt.End(xlToLeft)
                rngTgt.Offset(0, 1).FormulaR1C1 = "=day(r[1]c[-1])"
                rngTgt.Offset(0, 2).FormulaR1C1 = "=month(r[1]c[-2])"
                Exit Do
            Else
                rngTgt.Offset(0, -2).FormulaR1C1 = "=day(r[1]c[-1])"
                rngTgt.Offset(0, -1).FormulaR1C1 = "=month(r[1]c[-2])"
            End If
        Loop Until Len(rngTgt.Value) = 0
        wksTgt.Rows(rngTgt.Row).Value = wksTgt.Rows(rngTgt.Row).Value
        wksTgt.Rows(rngTgt.Row + 1).Delete
        Set rngTgt = rngTgt.Offset(1).End(xlToLeft)
        Set rngSrc = rngSrc.End(xlToLeft).End(xlDown).End(xlDown)
    Loop Until rngSrc.Row = wksSrc.Rows.Count
    'add numbered headers
    lngLoop = 1
    Set rngTgt = wksTgt.Cells(1, 9)
    Do
        rngTgt.Value = lngLoop & " Firstname"
        rngTgt.Offset(0, 1).Value = lngLoop & " day"
        rngTgt.Offset(0, 2).Value = lngLoop & " month"
        Set rngTgt = rngTgt.Offset(0, 3)
        lngLoop = lngLoop + 1
    Loop Until rngTgt.Column > wksTgt.UsedRange.Columns.Count
End Sub

Open in new window

0
 

Author Closing Comment

by:Th0R
ID: 39660534
You are one of the reasons this site is so great.
Works like a charm!

Couldn't have done it without you!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39661882
You are ONE OF the reasonS this site is so great.

^ Fixed that for you.
0

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
Starting up a Project

688 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