I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1. Is there somebody who
can help me out? Thanks!


Landa wrote...
Let me illustrate my question in detail: Let say:
Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
Under each column, there are terms for the categories. E.g.
A2: Apple, A3: Orange, A4: Lemon
B2: Cake; B3: Chocolate
C2: Coffee, C3: Tea
What I want to do is to put all the category in Column A, and all the
corresponding terms in Column B.
i.e.
Fruit Apple
Fruit Orange
Fruit Lemon
Dessert Cake
Dessert Chocolate
Drinks Coffee
Drinks Tea
Is there anyone who can help me to do this in a faster way? Of course, I can
move the terms to the desired boxes manually, but having several hundreds of
categories, it's really time-consuming. Thanks a lot!!

For the heck of it, formulas to do this.

If your table in A1:C4,

Fruit____Dessert___Drinks
Apple___Cake_____Coffee
Orange__Chocolate_Tea
Lemon_________________

were named Tbl, and the top-left result cell were A11, try these
formulas.

A11:
=INDEX(Tbl,1,1)

B11:
=INDEX(Tbl,2,1)

A12:
=IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH (A11,
INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))

B12:
=INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX (Tbl,1,0),0))

Select A12:B12 and fill down until the formulas return #REF! .

Hi Landa,

One way is to select the cells and go to

Insert - Name - Define

Here you can give it a name and click Add and Ok.

Regards,
Bondi

"Landa" wrote:
I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1

First, a quick correction: .. Harlan is hardly a "user" <g,
He's a very senior responder / contributor to many newsgroups, an excel
grandmaster / maestro who has posted tens of thousands of responses over the
years!

Ok .. One quick way to define ranges is to use the namebox
(the box with the drop arrow just to the left of the formula bar)

There are 2 defined / named ranges that Harlan describes in his response

One is: Tbl

Select A1:C4, then click inside the namebox
Key-in the name: Tbl, then press ENTER
(Note that the name is spelt: Tbl (with a lowercase "L", not: Tb1)

The 2nd one is : All
Select A1, then click inside the namebox
Key-in the name: All, then press ENTER

The normal way to create defined ranges is via clicking:
Insert Name Define
(as pointed by the other responder)

The options are there to create and delete defined ranges

There are also dynamic ranges - defined ranges which expand and contract
automatically - which must be created via this route.

And in case needed,
here's a quick sample implementation of Harlan's response:
http://cjoint.com/?fqj1Uge4fv
Landa_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Landa" wrote:
I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1. Is there somebody who
can help me out? Thanks!


Landa wrote...
Let me illustrate my question in detail: Let say:
Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
Under each column, there are terms for the categories. E.g.
A2: Apple, A3: Orange, A4: Lemon
B2: Cake; B3: Chocolate
C2: Coffee, C3: Tea
What I want to do is to put all the category in Column A, and all the
corresponding terms in Column B.
i.e.
Fruit Apple
Fruit Orange
Fruit Lemon
Dessert Cake
Dessert Chocolate
Drinks Coffee
Drinks Tea
Is there anyone who can help me to do this in a faster way? Of course, I can
move the terms to the desired boxes manually, but having several hundreds of
categories, it's really time-consuming. Thanks a lot!!

For the heck of it, formulas to do this.

If your table in A1:C4,

Fruit____Dessert___Drinks
Apple___Cake_____Coffee
Orange__Chocolate_Tea
Lemon_________________

were named Tbl, and the top-left result cell were A11, try these
formulas.

A11:
=INDEX(Tbl,1,1)

B11:
=INDEX(Tbl,2,1)

A12:
=IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH (A11,
INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))

B12:
=INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX (Tbl,1,0),0))

Select A12:B12 and fill down until the formulas return #REF! .

Thank you soooo much, Max, Harlan the Excel Grandmaster and Bondi!!!!! You
guys have really helped me save a lot of my time! Now I can go for a coffee
break. Cheers ^0^


"Max" wrote:

"Landa" wrote:
I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1

First, a quick correction: .. Harlan is hardly a "user" <g,
He's a very senior responder / contributor to many newsgroups, an excel
grandmaster / maestro who has posted tens of thousands of responses over the
years!

Ok .. One quick way to define ranges is to use the namebox
(the box with the drop arrow just to the left of the formula bar)

There are 2 defined / named ranges that Harlan describes in his response

One is: Tbl

Select A1:C4, then click inside the namebox
Key-in the name: Tbl, then press ENTER
(Note that the name is spelt: Tbl (with a lowercase "L", not: Tb1)

The 2nd one is : All
Select A1, then click inside the namebox
Key-in the name: All, then press ENTER

The normal way to create defined ranges is via clicking:
Insert Name Define
(as pointed by the other responder)

The options are there to create and delete defined ranges

There are also dynamic ranges - defined ranges which expand and contract
automatically - which must be created via this route.

And in case needed,
here's a quick sample implementation of Harlan's response:
http://cjoint.com/?fqj1Uge4fv
Landa_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Landa" wrote:
I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1. Is there somebody who
can help me out? Thanks!


Landa wrote...
Let me illustrate my question in detail: Let say:
Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
Under each column, there are terms for the categories. E.g.
A2: Apple, A3: Orange, A4: Lemon
B2: Cake; B3: Chocolate
C2: Coffee, C3: Tea
What I want to do is to put all the category in Column A, and all the
corresponding terms in Column B.
i.e.
Fruit Apple
Fruit Orange
Fruit Lemon
Dessert Cake
Dessert Chocolate
Drinks Coffee
Drinks Tea
Is there anyone who can help me to do this in a faster way? Of course, I can
move the terms to the desired boxes manually, but having several hundreds of
categories, it's really time-consuming. Thanks a lot!!

For the heck of it, formulas to do this.

If your table in A1:C4,

Fruit____Dessert___Drinks
Apple___Cake_____Coffee
Orange__Chocolate_Tea
Lemon_________________

were named Tbl, and the top-left result cell were A11, try these
formulas.

A11:
=INDEX(Tbl,1,1)

B11:
=INDEX(Tbl,2,1)

A12:
=IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH (A11,
INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))

B12:
=INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX (Tbl,1,0),0))

Select A12:B12 and fill down until the formulas return #REF! .

Glad it helped !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Landa" wrote:
Thank you soooo much, Max, Harlan the Excel Grandmaster and Bondi!!!!! You
guys have really helped me save a lot of my time! Now I can go for a coffee
break. Cheers ^0^