Moving Excel row values into coumn values

Closed
XcelBegnr
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010
- Apr 15, 2010 at 04:20 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Apr 20, 2010 at 03:47 PM
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

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

Apr 15, 2010 at 04:23 PM
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.
0
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.
0
XcelBegnr
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Apr 15, 2010 at 10:12 PM
Ray, thanks for your response. That won't work as the the no of rows and columns will vary.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 15, 2010 at 08:37 PM
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
0
XcelBegnr
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Apr 15, 2010 at 10:13 PM
Rizvisa1, thank you. Let me try and will get back with the result.
0
XcelBegnr
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Apr 19, 2010 at 09:34 AM
Thank you!

Grand It worked except one caveat i.e. it seems to write over the input cells. I might not have been clear in original description. What changes need to be made so the output (for example) starts at cell F10 onwards and the columns for date and hours are written in L and M columns (L10 and M10 has the header - "Date", "Hours")?

I was looking to understand / find out more about some of the comands you used (e.g. Cells w/ xlToLeft etc.). Can you recommend an online refereence website / resource which has all the Excel macro commands?

Many thanks!

-Joe
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 19, 2010 at 10:22 AM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.

For macro, I found the best way to learn is use macro recorder that comes with excel. 2nd would be when you get stuck, use google. What I know comes from experience, recorder and google

Put up a sample file with how a typical file would look like ( you can fake data if you like, just have the layout correct and a good representative how how a file would typical use. I will modify the code for you and this time I will put comments to explain what code is doing.
0
XcelBegnr
Posts
9
Registration date
Thursday April 15, 2010
Status
Member
Last seen
April 20, 2010

Apr 19, 2010 at 12:26 PM
done...sample file "testdata1.xlsx" uploaded. I also added a tab for explanations of the data and layout.

Thank you.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Apr 19, 2010 at 12:48 PM
Could you paste the link to the file over here.
0