Moving Excel row values into coumn values Closed

Question

Hi All,

I am trying to move data from rows into columns which may be described with table better - 

Name	Project	4/10	4/11	4/12	
								
Joe	ProjX	8	10	5		
Peter	ProjY	5               8                           


In the above table, 1st row is the header row and in the output last 3 column names would become the row values in a column called Date and the integer values would come under a column called Hours. In other words the output should look like the following:

Name	Project	Date	Hours
Joe	ProjX	4/10	8
Joe	ProjX	4/11	10
Joe	ProjX	4/12	5	
Peter	ProjY	4/10	5
Peter	ProjY	4/11	8

Can someone please help with this? Many Thanks.

Regards
-Joe

Configuration: Windows XP / Internet Explorer 8.0

XcelBegnr · Answer

Noticed after posting that it looks little confusing due to formatting. Input data looks like what someone may enter timesheet at i.e. name, project name, no of hours for the day/date, no.of hours for next day/date, and so on.

Ray · Answer

Select the Data you want to do this on,  cut it, then Paste and pick the transpose suboption.   I found this answer in the Help.

rizvisa1 · Answer

Try this macro


Sub FixRow()
Dim lMaxRows As Long ' max rows of data
Dim lRowBeanCounter As Long ' a row counter
Dim iColCount As Integer 'to see how many columns are used on the row
Dim iColStart As Integer 'the hour starts from this column number
Dim iHourData As Integer ' number of hour data


    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    lRowBeanCounter = 1
    iColStart = 3
    
    For lRowBeanCounter = lMaxRows To 2 Step -1
        
        iColCount = Cells(lRowBeanCounter, Columns.Count).End(xlToLeft).Column
        
        iHourData = iColCount - iColStart
        
        
        If iHourData >= 0 Then
        
            If (iHourData > 0) Then
                Rows(lRowBeanCounter + 1 & ":" & lRowBeanCounter + iHourData).Insert
            End If
        
            Range(Cells(lRowBeanCounter, 1), Cells(lRowBeanCounter, iColStart - 1)).Copy
            Range(Cells(lRowBeanCounter, 1), Cells(lRowBeanCounter + iHourData, iColStart - 1)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False
            
            Range(Cells(lRowBeanCounter, iColStart), Cells(lRowBeanCounter, iColCount)).Copy
            Cells(lRowBeanCounter, iColCount + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
            Range(Cells(1, iColStart), Cells(1, iColCount)).Copy
            Cells(lRowBeanCounter, iColCount + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
            Range(Cells(lRowBeanCounter, iColStart), Cells(lRowBeanCounter + iHourData, iColCount)).Delete Shift:=xlToLeft
        
        End If
        
    Next lRowBeanCounter
    
    iColCount = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Range(Cells(1, iColStart), Cells(1, iColCount)).Clear
    
    Range("C1") = "Date "
    Range("D1") = "Hours"

End Sub

Moving Excel row values into coumn values

3 responses

Similar discussions

Subscribe To Our Newsletter!