About sorting and extracting data in one column
Ting,
When building the formula I indeed provided more groups than just A
and B. This is the spirit of the aux column in B:B.
Are you starting your crosstab from D1? If not, wherever you see a D1
in the formula you have to change it to the cell from which you are
starting.
Post back with your current formula, copied/pasted exactly as you are
using it.
HTH
Kostis
On Jun 8, 10:02 am, Ting Li wrote:
About the situation I just mentioned, C won't come up with a new column and
it stays at the column same as the previous result:
B A B C
D
B 1 B A B A
B 2 B A
C
C 3 B
C ---4 C
A 5 C
A
B
A
C
Please help~ m(_ _)m
"Ting Li" wrote:
Thanks for your repy!!!
But when I copy the formula to the cell D1, it showed an error message and
said maybe the formula error.
Also, the situation now becomes:
B
B
B
C
A
A
B
A
C
Is that possible for me to use similar functions for tabulation?
"vezerid" wrote:
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
|