How can I move several columns of words into one column in Excel?

[Guest]

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

 

[Pete_UK]

I can see how to do this manually, and it shouldn't take too long.
Insert a new column A, then click on B1 (Fruit) and click <copy> and
paste into A2 to A-whatever, where "whatever" is the number of cells
occupied in column B.

Then click on C2, hold down <shift>, press <End> once followed by
<down-arrow> then release <shift> (this will have highlighted all the
contiguous data in column C), then click <copy>, move cursor to bottom
of column B data and paste the desserts directly under the fruits. Then
copy the word Dessert from C1 to Awhatever+1 down to as many items as
there are now in column B.

You can do a similar thing for the Drinks which are now in column D,
i.e. copy the the data to the bottom of column B then copy the word
Drinks into column A as required. You can then delete columns C and D.
This should only take a few minutes at most.

Hope this helps.

Pete

 

[Guest]

Thanks Pete for your quick reply! I was actually doing very similar thing as
you've mentioned. I highlighted the data in a column and drag it to Column B
for all the columns and drag all the 'categories' in column A. But it took me
like an hour to do this since I had several hundred of categories (columns),
and there were tens to hundreds of terms under each category. So you know,
you have to roll up and down to highlight and drag the data etc... I am
wondering if there is a faster way to do it the next time.
But thanks for your suggestion, Pete!

 

[Pete_UK]

Really, it should be a matter of minutes to do this. If you have large
movements to make it is often better to use the keyboard rather than
the mouse to move around - pressing the Page Down key a few times while
holding the Shift key down will take you to the bottom of several
hundred rows very quickly, even if there are gaps in that column - once
the data is highlighted in this way it is easy to click <copy> or <cut>
then move to the bottom of column B and press <Enter>. A few
key-presses only.

If you need to repeat the process quite frequently then you could think
about recording a macro while you do it once, but ensure that you do so
with the Relative Address mode selected. Then in future you could just
re-run the macro - you won't get particularly efficient code by
recording the macro, but if it works then this may not be a great
concern for you.

Hope this helps.

Pete

 

[Harlan Grove]

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

 

[Guest]

Hi Harlan, thanks so much for your reply. I was out of town and am excited
now to find a solution. But forgive me I am not very good at excel. Would you
mind telling me how to name the selected cell (say A1:C4) as TB1.

Thanks a lot!

 

[Guest]

Thank you very much, Pete! I will try out your suggestion!