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! .