Solved

Manipulating excel data to new format

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

Expert Comment

by:aikimark
ID: 39656416
Why are you dropping the last name in the transformation process?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
A short article about problems I had with the new location API and permissions in Marshmallow
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now