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

Dataset Transformation

I'm trying to take a columnar excel dataset and transform it into a more usable report.  The attached file shows what I am trying to do but it will be done with many more rows of data.  I am completely stumped.

Thanks in advance!
transform-dataset.xls
0
pukleja74
Asked:
pukleja74
  • 8
  • 6
1 Solution
 
dlmilleCommented:
Ok - rather than a macro, here's a hands-on approach just using the power of excel.  This will work with as large a dataset as you have, in 4 simple steps, which are highlighted in the attached spreadsheet demo.

Let me know if this helps!  The steps APPEAR to be so straight forward, its faster than working up the macro to do it.  Unless you need to do this lots of times each day or something, then chat me back and we can work on that.

Dave
transform-dataset.xls
0
 
dlmilleCommented:
I've take a few moments and did a macro as well, in case this is repetative or for future use :)

Here's the code:
 
Sub transformDataset()
Dim mySheet As Worksheet, outCursor As Range
Dim myRow As Range
Dim func As String, grade As String, name As String, title As String, nameTitle As String


    Set mySheet = ActiveSheet 'change this to specific sheet if macro is to be run from some other place

    'gather the data
    For Each myRow In mySheet.Range("A2", mySheet.Range("A" & Rows.Count).End(xlUp)) 'assume header row 1
        Set outCursor = myRow.Cells(1, 1)
        func = outCursor.Value
        grade = outCursor.Offset(0, 1).Value
        name = outCursor.Offset(0, 2).Value
        title = outCursor.Offset(0, 3).Value
        
        'clear the output row first
        outCursor.Resize(1, 4).Value = ""
        
        'now fix the row of output
        outCursor.Value = grade
        
        nameTitle = name & " (" & title & ")"
        
        If func = "IT" Then
            outCursor.Offset(0, 1).Value = nameTitle
        Else
            outCursor.Offset(0, 2).Value = nameTitle
        End If
        
        Set outCursor = outCursor.Offset(1, 0)
    
    Next myRow
    
    mySheet.Range("A1:D1").Value = Split("Grade,IT,FINANCE,", ",") 'create new header
                
End Sub

Open in new window


See attached and run the macro on the first sheet to see the transformation "live".

Enjoy!

Dave
transform-dataset-r1.xls
0
 
pukleja74Author Commented:
Hi Dave!

Thanks for taking some time to help.  This isn't quite what I was looking to do.  I too did the same thing that you did but still ended up where did.  The additional step is that  I need to eliminate the empty rows so that all the grade 25s start on the same row and then all the 24s start on the same row.  Each function may have a varying number of job titles.  Also, this has to work for 30 functions and 10k rows of data.

Thanks,
Phil
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.

 
dlmilleCommented:
That's not what your original posted spreadsheet does.  E.g., you have 2 25's and they're on two rows.  By the way, my solution is an exact match to what you originally specified.

I suggest you start with that and re-post a visible example, then we can work from that.

Cheers,

Dave
0
 
pukleja74Author Commented:
Dave,

Sorry for the delay.  I was out of town and wasn't able to get to the response.  Actually I have 3 25s that are on 2 rows.

What I need to do is group all the same grade so that they are visible horizonatally without all the white space.  This seems easy but I can't get past my mental block on this one.

Phil
0
 
dlmilleCommented:
It should be - hopefully :)

So if there are multiple functions with the same grade, that would generate new lines?  E.g., Finance has 2 at grade 25.

Dave
0
 
dlmilleCommented:
Ok - the code could be optimized, but I left it alone assuming it would be easier to follow.  First, I sort the data by grade, load an array of position (func, grade, name, title, nameTitle) and then I clear the data, and start generating output, by grade.  If a position is filled, proceeding to the next line, otherwise using current.

Assumes data is in columns A:D and starts with header on A1, with nothing else below.

Let me know if this helps!  The output matches your desired output from original question - thanks for pointing out I had misread your output, initially!

Here's the code:
 
Option Explicit
Type position
    func As String
    grade As String
    name As String
    title As String
    nameTitle As String
End Type
Sub transformDataset()
Dim mySheet As Worksheet, outCursor As Range
Dim myRow As Range
Dim myPosition() As position
Dim func As String, grade As String, name As String, title As String, nameTitle As String
Dim i As Long
    
    Set mySheet = ActiveSheet 'change this to specific sheet if macro is to be run from some other place

    'sort the data by grade, first
    mySheet.Sort.SortFields.Clear
    mySheet.Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With mySheet.Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    'gather the data
    For Each myRow In mySheet.Range("A2", mySheet.Range("A" & Rows.Count).End(xlUp)) 'assume header row 1
        Set outCursor = myRow.Cells(1, 1)
        func = outCursor.Value
        grade = outCursor.Offset(0, 1).Value
        name = outCursor.Offset(0, 2).Value
        title = outCursor.Offset(0, 3).Value
        
        ReDim Preserve myPosition(i) As position
        
        myPosition(i).func = func
        myPosition(i).grade = grade
        myPosition(i).name = name
        myPosition(i).title = title
        myPosition(i).nameTitle = name & " (" & title & ")"
                   
        i = i + 1
    
    Next myRow
    
    Range("A:D").Clear
    mySheet.Range("A1:D1").Value = Split("Grade,IT,FINANCE,", ",") 'create new header
    
    Set outCursor = mySheet.Range("A2")
    
    i = 0
    
    Do While i <= UBound(myPosition)
        If outCursor.Value = "" Then
            outCursor.Value = myPosition(i).grade
            If myPosition(i).func = "IT" Then
                outCursor.Offset(0, 1).Value = myPosition(i).nameTitle
            Else
                outCursor.Offset(0, 2).Value = myPosition(i).nameTitle
            End If
            i = i + 1
        Else
            If outCursor.Offset(0, 1) = "" And myPosition(i).func = "IT" Then
                'paste the IT title
                outCursor.Offset(0, 1).Value = myPosition(i).nameTitle
                i = i + 1
            ElseIf outCursor.Offset(0, 2) = "" And myPosition(i).func = "FINANCE" Then
                'paste the FINANCE title
                outCursor.Offset(0, 2).Value = myPosition(i).nameTitle
                i = i + 1
            Else 'proceed to next row and start again
                Set outCursor = outCursor.Offset(1, 0)
            End If
        End If
    Loop
                
End Sub

Open in new window

See attached completed solution.

Enjoy!

Dave
transform-dataset-r2.xls
0
 
pukleja74Author Commented:
Finally figured it out.  Here is how I did the code.
transform-dataset-3.xls
0
 
dlmilleCommented:
The output you generate was NOT what you requested in the asking question, nor followup, nor in your example spreadsheet output.

Why is that?

Dave
0
 
pukleja74Author Commented:
Actually, it certainly IS the essence of my question.  The code that I ultimately developed just takes it a step further with some nice formatting.  There are multiple ways to get to the same answer for a question.

Phil
0
 
dlmilleCommented:
My comment was not as graceful as I would have liked. Apologies.

Please help me understand how I missed this.  Its not about the points, but it is about time/effort into something to find out I've missed!

What's interesting is when I look at my output, and the output you posted in the spreadsheet, its eerie, because it looks the same...

Using the original dataset, here's the output I get from both yours and my macro:

 Compare two solutions
Now, not accounting for the fact you put same titles in same cells with CRLF, which was not requested, lets' just look at the output....

Your output is missing the IT EE #2 in PSG 26, for some reason (you might want to check your code)  Aside from that, the output looks "the same" for all intensive purposes.

Using the dataset you have presented for your solution, here's the output from both yours and my macro:

 Compare two solutions, new dataset
Again, you might want to check your code, as you are missing one of your titles in your output with your new dataset, as well...

When I look at this comparison, again formatting aside, and the omission of title in yours, Again, they appear pretty eerily similar (note HR function added, so addition of new functions was a new one on me - it would have helped me provide a solution for additional functions - pretty easy change in my macro.  Just a suggestion to provide a bit more into your requirements, as I stand firm in my beliefs that I met your specification exactly - please advise what I'm missing.
Since you appear happy with your solution, allow me to assist, to ensure you don't omit any titles (again, note, you have 12 titles in your output matrix, while the dataset has 13)...


Achieved by adding 1 to iLastRow.

See attached, your solution repaired.

No objections, and glad I could at least help you toward the solution you desired.

Dave
0
 
dlmilleCommented:
Whoops - here's the attached revision to your solution.

Enjoy!

Dave
transform-dataset-3b.xls
0
 
pukleja74Author Commented:
Dave,

In reviewing both of our responses, it looks like we both could have been a little more graceful in our responses.

I think that your subsequent code definitely responded to my request but I was looking for something more dynamic which is what my code provides.

As for as I'm concerned, a resolution was reached.

Phil
0
 
pukleja74Author Commented:
The code that was posted by the members wasn't quite what I was looking to do.  Figured out the solution on my own.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now