Selected rows change to separate column
I'd like to change some rows, which exist in same column, to separate column.
e.g Original Data is as follows:
ColumnA ColumnB ColumnC
Monday
Cookie AS 2
Bread SMY 4
Pudding FMI 7
Friday
Cookie AS 2
Bread SMY 4
Pudding FMI 7
Required format is as follows:
ColumnA ColumnB ColumnC ColumnD
Cookie AS 2 Monday
Bread SMY 4 Monday
Pudding FMI 7 Monday
Cookie AS 2 Friday
Bread SMY 4 Friday
Pudding FMI 7 Friday
The reason is I want to sort by Monday, Friday etc.
Pls, see the attached details.
| Attachment | Size |
|---|---|
| Selected Row to Column Change.xlsx | 10.83 KB |
Use can use this formula in
Use can use this formula in Cell D2 and drag it across rows...
Assuming the data starts from Cell A1.
=IF(C2="",A2,D1)
Drag the same formula in Cell D1 as well.
Use this code to format the
Use this code to format the data...
Sub FormatData()
Dim rngData As Range
Dim rngCell As Range
Dim strDay As String
Set rngData = Sheet1.Range("A1:C8")
For Each rngCell In rngData.Rows
If rngCell.Cells(, 2) = vbNullString Then
strDay = rngCell.Cells(, 1)
Else
rngCell.Cells(, 4) = strDay
End If
Next rngCell
End Sub