• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Manipulating excel data to new format

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
Th0R
Asked:
Th0R
  • 3
  • 2
  • 2
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
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
 
Th0RAuthor Commented:
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
 
aikimarkCommented:
Why are you dropping the last name in the transformation process?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Th0RAuthor Commented:
Because its not used in the new system
0
 
aikimarkCommented:
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
 
Th0RAuthor Commented:
You are one of the reasons this site is so great.
Works like a charm!

Couldn't have done it without you!
0
 
[ fanpages ]IT Services ConsultantCommented:
You are ONE OF the reasonS this site is so great.

^ Fixed that for you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now