Solved

Manipulating excel data to new format

Posted on 2013-11-17
7
461 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 45

Expert Comment

by:aikimark
ID: 39656416
Why are you dropping the last name in the transformation process?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

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

Accepted Solution

by:
aikimark earned 500 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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