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