advance level of sorting data? by Ting

[Guest]

I have got a column of data like below:
Y
Y
Z
X
Z
X
Y
X

how could I use the excel function to make it becomes the following?
A B C D
1 Y X Y X
2 Y Z
3 Z X


The rule is,
1) once there is a change from X to Y / Y to X , new column will be applied.
2) Z will stick with the previous result in the same column

Please help cause I have so many data need to be proceed now

 

[NickHK]

Not tested much, but something like this ?

Private Sub CommandButton1_Click()
Dim SortRange As Range
Dim DestinationRange As Range
Dim RowOffset As Long
Dim ColOffset As Long
Dim Cell As Range
Dim OldChar As String

Const DEST_RANGE As String = "B1"
Const STICKY_CHARS As String = "Z" 'Or "Z,T,etc

Set SortRange = Range("A1:A8")
Set DestinationRange = Range(DEST_RANGE)

For Each Cell In SortRange
'Cell.Select
If (InStr(1, Cell.Value, STICKY_CHARS) > 0) Or OldChar = Cell.Value Then
RowOffset = RowOffset + 1
Else
OldChar = Cell.Value
RowOffset = 0
ColOffset = ColOffset + 1
End If
Range(DEST_RANGE).Offset(RowOffset, ColOffset).Value = Cell.Value
Next

End Sub

NickHK