Assume your data is in A1:A19. Assume you start your cross-tabulation
in D1. Before this, use an aux column, in B:B, with the follwoing: In
B1 enter 1. Then in B2:
=IF(A2=A1,B1,B1+1)

Now, in D1, enter the following *array* formula (commit with Shift+Ctrl
+Enter)

=IF(ISERROR(SMALL(IF($B$1:$B$19=COLUMNS($D$1:D1),R OW($A$1:$A$19)-ROW($A
$1)+1),ROW()-ROW(D$1)+1)),"",INDEX($A$1:$A$19,SMALL(IF($B$1:$B
$19=COLUMNS($D$1:D1),ROW($A$1:$A$19)-ROW($A$1)+1),ROW()-ROW(D$1)+1)))

Copy to the right and down as far as necessary.

HTH
Kostis Vezerides


On Jun 7, 10:28 am, Ting Li wrote:
Thank you so much for your help.
But I want to show each of them in independent boxes like:
A B C D E F
1 B A B A
2 B A
3 A
4
5

Since my data base has more than 1000 entries, I am now looking for some
fast ways to change them into the pattern like above.

"Gary''s Student" wrote:
Use:

=A1 & " " & A3 & " " & A1 & " " & A3
=A2 & " " & A4
=" " & A3

--
Gary''s Student - gsnu200727

"Ting Li" wrote:

I have got a column of data like below:
B
B
A
A
A
B
A

how could I use the excel function to make it becomes the following?
B A B A
B A
A