Moving Excel row values into coumn values

[Closed]
Report
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

3 replies

Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

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.
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.
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Ray, thanks for your response. That won't work as the the no of rows and columns will vary.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you repost with the modified macro that you have been using. The macro I gave you would not work with the sheet of data you gave. Instead of reinventing the wheel, I would like to see how macro impacts your report when you run it

Also does your sample sheet looks exactly like real sheet
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

I did not modify the macro, i tested one you sent as-is on the sample data I posted originally.

The file with sample data posted is exact in configuration to what would be real data.
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Hi Rizvisa1, any luck with the code?

would you mind just showing in code how can I determine no. of rows filled in (i.e. 4) from D22-D25 of the sample data file?

Thank you.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
lLastRow = Range("D22").End(xlDown).Row

This will give you the last row where there is data after D22. Now if there is gap in row, meaning
d23 has data, d24 is blank and d25 has data, it will give you d23, So there should not be any blankcell in between