software

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

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 lt;copygt; and
paste into A2 to A-whatever, where quot;whateverquot; is the number of cells
occupied in column B.

Then click on C2, hold down lt;shiftgt;, press lt;Endgt; once followed by
lt;down-arrowgt; then release lt;shiftgt; (this will have highlighted all the
contiguous data in column C), then click lt;copygt;, 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.

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

quot;Pete_UKquot; wrote:

gt; I can see how to do this manually, and it shouldn't take too long.
gt; Insert a new column A, then click on B1 (Fruit) and click lt;copygt; and
gt; paste into A2 to A-whatever, where quot;whateverquot; is the number of cells
gt; occupied in column B.
gt;
gt; Then click on C2, hold down lt;shiftgt;, press lt;Endgt; once followed by
gt; lt;down-arrowgt; then release lt;shiftgt; (this will have highlighted all the
gt; contiguous data in column C), then click lt;copygt;, move cursor to bottom
gt; of column B data and paste the desserts directly under the fruits. Then
gt; copy the word Dessert from C1 to Awhatever 1 down to as many items as
gt; there are now in column B.
gt;
gt; You can do a similar thing for the Drinks which are now in column D,
gt; i.e. copy the the data to the bottom of column B then copy the word
gt; Drinks into column A as required. You can then delete columns C and D.
gt; This should only take a few minutes at most.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;

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 lt;copygt; or lt;cutgt;
then move to the bottom of column B and press lt;Entergt;. 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.

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

quot;Harlan Grovequot; wrote:

gt; Landa wrote...
gt; gt;Let me illustrate my question in detail: Let say:
gt; gt;Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
gt; gt;Under each column, there are terms for the categories. E.g.
gt; gt;A2: Apple, A3: Orange, A4: Lemon
gt; gt;B2: Cake; B3: Chocolate
gt; gt;C2: Coffee, C3: Tea
gt; gt;What I want to do is to put all the category in Column A, and all the
gt; gt;corresponding terms in Column B.
gt; gt;i.e.
gt; gt;Fruit Apple
gt; gt;Fruit Orange
gt; gt;Fruit Lemon
gt; gt;Dessert Cake
gt; gt;Dessert Chocolate
gt; gt;Drinks Coffee
gt; gt;Drinks Tea
gt; gt;Is there anyone who can help me to do this in a faster way? Of course, I can
gt; gt;move the terms to the desired boxes manually, but having several hundreds of
gt; gt;categories, it's really time-consuming. Thanks a lot!!
gt;
gt; For the heck of it, formulas to do this.
gt;
gt; If your table in A1:C4,
gt;
gt; Fruit____Dessert___Drinks
gt; Apple___Cake_____Coffee
gt; Orange__Chocolate_Tea
gt; Lemon_________________
gt;
gt; were named Tbl, and the top-left result cell were A11, try these
gt; formulas.
gt;
gt; A11:
gt; =INDEX(Tbl,1,1)
gt;
gt; B11:
gt; =INDEX(Tbl,2,1)
gt;
gt; A12:
gt; =IF(COUNTIF(A$11:A11,A11)lt;COUNTA(INDEX(Tbl,0,MATCH (A11,
gt; INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0) 1))
gt;
gt; B12:
gt; =INDEX(Tbl,COUNTIF(A$11:A12,A12) 1,MATCH(A12,INDEX (Tbl,1,0),0))
gt;
gt; Select A12:B12 and fill down until the formulas return #REF! .
gt;
gt;

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

quot;Pete_UKquot; wrote:

gt; Really, it should be a matter of minutes to do this. If you have large
gt; movements to make it is often better to use the keyboard rather than
gt; the mouse to move around - pressing the Page Down key a few times while
gt; holding the Shift key down will take you to the bottom of several
gt; hundred rows very quickly, even if there are gaps in that column - once
gt; the data is highlighted in this way it is easy to click lt;copygt; or lt;cutgt;
gt; then move to the bottom of column B and press lt;Entergt;. A few
gt; key-presses only.
gt;
gt; If you need to repeat the process quite frequently then you could think
gt; about recording a macro while you do it once, but ensure that you do so
gt; with the Relative Address mode selected. Then in future you could just
gt; re-run the macro - you won't get particularly efficient code by
gt; recording the macro, but if it works then this may not be a great
gt; concern for you.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;