Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Excel?
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Excel?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Exc
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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Exc
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Excel?
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! . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Exc
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! :) "Harlan Grove" wrote: 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! . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I move several columns of words into one column in Exc
Thank you very much, Pete! I will try out your suggestion!
"Pete_UK" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add more columns in Excel past column IV | Excel Discussion (Misc queries) | |||
Can I add more columns in Excel past column IV | Excel Discussion (Misc queries) | |||
Can I add more columns in Excel past column IV | Excel Discussion (Misc queries) | |||
In Excel, sorting columns automatically by clicking column title | Excel Discussion (Misc queries) | |||
How do I make a column in Excel into two columns for only A3-A8 | Excel Discussion (Misc queries) |