Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Manipulating excel data to new format

Posted on 2013-11-17
7
Medium Priority
?
493 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Make the most of your online learning experience.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
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…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

972 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