Excel - Moving row values into column values
Issue
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
Solution
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
Note
Thanks to [rizvisa1] for this tip on the forum.
Related
- Excel - Moving row values into column values
- Excel - Count 2 values in one column and 1 value in other - How-To - Excel
- How to name excel sheets on values in columns ✓ - Forum - Excel
- Excel insert row between different values ✓ - Forum - Excel
- Compare two columns in excel for missing values - Forum - Excel
- Excel hide false values ✓ - Forum - Office Software
This document, titled « Excel - Moving row values into column values », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).