Transpose Set of Values through MACRO

[sarojksahoo007]

Hi,

Working on set of data. Where i want data should transpose in below format.
RollNo Designation Name 1-Feb-17 2-Feb-17 3-Feb-17 4-Feb-17 5-Feb-17
'3300000006 Console Operator Gayani Raghavendra HD A P A P
'3300000004 Security Guard S Jaipal HD P P P A
'3300000005 Security Guard P Siva A A A A A
'3300000007 Security Guard GlV Suman P P P P A
'3300000008 Security Guard M Murali P A P P P
'3300000009 Security Guard Jitu Gohain P P P P P
'3300000010 Security Guard Shampak Bora P P P P A
'3300000011 Security Guard M Rajukumar A A P A HD
'3300000012 Security Guard Prakash Borah A A A P P
'3300000014 Security Guard Dibakar Kundu P P P A P




Data RollNo Designation Name Attendance
1-Feb-17 '3300000006 Console Operator Gayani Raghavendra HD
1-Feb-17 '3300000004 Security Guard S Jaipal HD
1-Feb-17 '3300000005 Security Guard P Siva A
1-Feb-17 '3300000007 Security Guard GlV Suman P
1-Feb-17 '3300000008 Security Guard M Murali P
1-Feb-17 '3300000009 Security Guard Jitu Gohain P
1-Feb-17 '3300000010 Security Guard Shampak Bora P
1-Feb-17 '3300000011 Security Guard M Rajukumar A
1-Feb-17 '3300000012 Security Guard Prakash Borah A
1-Feb-17 '3300000014 Security Guard Dibakar Kundu P
2-Feb-17 '3300000006 Console Operator Gayani Raghavendra A
2-Feb-17 '3300000004 Security Guard S Jaipal P
2-Feb-17 '3300000005 Security Guard P Siva A
2-Feb-17 '3300000007 Security Guard GlV Suman P
2-Feb-17 '3300000008 Security Guard M Murali A
2-Feb-17 '3300000009 Security Guard Jitu Gohain P
2-Feb-17 '3300000010 Security Guard Shampak Bora P
2-Feb-17 '3300000011 Security Guard M Rajukumar A
2-Feb-17 '3300000012 Security Guard Prakash Borah A
2-Feb-17 '3300000014 Security Guard Dibakar Kundu P
3-Feb-17 '3300000006 Console Operator Gayani Raghavendra P
3-Feb-17 '3300000004 Security Guard S Jaipal P
3-Feb-17 '3300000005 Security Guard P Siva A
3-Feb-17 '3300000007 Security Guard GlV Suman P
3-Feb-17 '3300000008 Security Guard M Murali P
3-Feb-17 '3300000009 Security Guard Jitu Gohain P
3-Feb-17 '3300000010 Security Guard Shampak Bora P
3-Feb-17 '3300000011 Security Guard M Rajukumar P
3-Feb-17 '3300000012 Security Guard Prakash Borah A
3-Feb-17 '3300000014 Security Guard Dibakar Kundu P

 

[NARAYANK991]

Hi ,

Please upload your workbook with adequate data in it.

Narayan

 

[YasserKhalil]

Try this code

Sub Test()
    Dim arr    As Variant
    Dim temp    As Variant
    Dim i      As Long
    Dim j      As Long
    Dim k      As Long

    arr = Range("A1").CurrentRegion.Value
    ReDim temp(1 To UBound(arr, 1) * 5, 1 To 5)
    j = 1

    For k = 4 To 8
        For i = 2 To UBound(arr, 1)
            temp(j, 1) = arr(1, k)
            temp(j, 2) = arr(i, 1)
            temp(j, 3) = arr(i, 2)
            temp(j, 4) = arr(i, 3)
            temp(j, 5) = arr(i, k)
           
            j = j + 1
        Next i
    Next k

    Range("K1").Resize(, UBound(temp, 2)).Value = Array("Date", "RollNo", "Designation", "Name", "Value")
    Range("K2").Resize(UBound(temp, 1), UBound(temp, 2)).Value = temp
End Sub