Convert data layout macro

[jwst87]

Hi all,

I have a large amount of data showing time taken by employees performing specific tasks that is currently in the fallowing layout:

NAME Task1 Task2 Task3 Task4 Task5
James 25 5 0 20 45
Andrew 90 25 0 10 20
Sarah 120 30 0 5 70

I would like to convert it into the following layout to make it easier to reference and create pivot tables from:

NAME TASK Time
James Task1 25
James Task4 20
Sarah Task3 0

So far doing this manually has proven to be very time consuming, I was wondering if it was possible to create a macro to do the work for me?

Cheers

Jon

 

[Aaron Blood]

Yes, it's possible. Do you still need it?

 

[jwst87]

Yes please, any help building it would be much appreciated.

Jon

 

[Aaron Blood]

Which version of Excel?

 

[jwst87]

2003, I did try and edit OP but 10 mins expiry had passed

 

[Aaron Blood]

Assuming your data resides on Sheet1 and you have a blank Sheet2...

Sub Table_to_Record()
    Dim cell As Range, NextCell As Range, TargRange As Range
    Dim task As Integer
    Set TargRange = Sheet1.Range("A2:A4")
    Sheet2.Select
    For Each cell In TargRange
        For task = 1 To 5
            Set NextCell = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            NextCell.Value = cell.Value
            NextCell.Offset(0, 1).Value = Sheet1.Range("A1").Offset(0, task).Value
            NextCell.Offset(0, 2).Value = cell.Offset(0, task).Value
        Next task
    Next cell
End Sub

 

[lenze]

This will create a new table from Sheet1 onto Sheet2!!

Sub RearrangeMe()
Dim cl As Range
Dim LR As Long
Dim LR2 As Long
Dim LC As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For Each cl In Sheet1.Range("$A$2:$A" & LR)
LC = Sheet1.Cells(cl.Row, Columns.Count).End(xlToLeft).Column
For i = 2 To LC
   LR2 = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
   Sheet1.Cells(cl.Row, "A").Copy Sheet2.Cells(LR2, "A")
   Sheet1.Cells(1, i).Copy Sheet2.Cells(LR2, "B")
   Sheet1.Cells(cl.Row, i).Copy Sheet2.Cells(LR2, "C")
Next i
Next cl
End Sub

Adjust as needed!
lenze

 

[jwst87]

Thanks for your replies, i'm not sure what i'm doing wrong (i'm not particuarly expericenced in VBA) but I have run both macros with data in Sheet1 with a blank Sheet2. They both run without errors but I receive no results on Sheet2.

I should have added in my OP that the number of task headings and employess can vary from one table to another as they are team based so have different headings from eachother.

Thanks

Jon

 

[Aaron Blood]

Try this one then...

Sub Table_to_Record()
    Dim cell As Range, TargRange As Range, OutCell As Range
    Dim r As Integer, c As Integer
    Set TargRange = ActiveWindow.RangeSelection
    Set OutCell = Sheet1.Range("K4")
    For Each cell In TargRange.Resize(TargRange.Rows.Count - 1, 1).Offset(1, 0)
        For c = 1 To TargRange.Columns.Count - 1
            OutCell.Offset(r, 0).Value = cell.Value
            OutCell.Offset(r, 1).Value = TargRange(1, 1).Offset(0, c).Value
            OutCell.Offset(r, 2).Value = cell.Offset(0, c).Value
            r = r + 1
        Next c
    Next cell
End Sub

It's a little more flexible in that it just lets you select a table of any size (including row and column headings) and specify an output cell (K4) that can be on the same sheet if you like. Just make sure you select the range before you run the macro. I'm guessing the last version returned empty because maybe you didn't update the range references to reflect the location of your data?

 

[lenze]

How do the macros not work? I ran mine on your sample data

  A      B     C     D     E     F     
1 NAME   Task1 Task2 Task3 Task4 Task5 
2 James  25    5     0     20    45    
3 Andrew 90    25    0     10    20    
4 Sarah  120   30    0     5     70    
Sheet1
[Table-It] version 09 by Erik Van Geit

and got this on sheet 2

   A      B     C    
 1 Name   Task  Time 
 2 James  Task1 25   
 3 James  Task2 5    
 4 James  Task3 0    
 5 James  Task4 20   
 6 James  Task5 45   
 7 Andrew Task1 90   
 8 Andrew Task2 25   
 9 Andrew Task3 0    
10 Andrew Task4 10   
11 Andrew Task5 20   
12 Sarah  Task1 120  
13 Sarah  Task2 30   
14 Sarah  Task3 0    
15 Sarah  Task4 5    
16 Sarah  Task5 70   
Sheet2
[Table-It] version 09 by Erik Van Geit

I'm sure Aaron's code will also work

lenze