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

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

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

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

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

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

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

Kostis,

Yes. I did follow exactly what you told me to do.
=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)))

I copied this to the cell D1. I have also done the aux column at B:B and I
know it works as a tool that change pattern into numbers which would be
easier later to put them in columns.

Would you mind to email me the sample file?

"vezerid" wrote:

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