advance level of sorting data? by Ting

T

Ting Li

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
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top