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.

AttachmentSize
Selected Row to Column Change.xlsx10.83 KB
Vishesh's picture

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.

Vishesh's picture

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