| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Moving selected data
I have the following type of raw data:
Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 I need to rearrange it so that 'A' and 'B' Unit Types appear in the same row, where the Member no. and the Date are the same, otherwise, they remain in seperate rows. The result should look as follows: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 Any suggestions as to the easiest way of doing this would be appreciated. |
|
#2
|
|||
|
|||
|
... easiest way of doing this
A Pivot Table would bring you very close in a matter of clicks & drags .. Assuming this data below is in Sheet1 Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 Click anywhere within the data Click Data Pivot Table Report Next Next In step 3 of the wizard: drag Member No and drop within ROW area drag Date and drop within ROW area (below Member No) drag Type and drop within COLUMN area drag Value and drop within DATA area (it'll appear as Sum of Value) Double-click on Member No Select None under Subtotals OK Click Finish The Pivot Table (PT) will appear in a new sheet to the left of Sheet1 In the PT: Right-click on the vertical "Grand Total" label Delete Right-click on the horiz "Grand Total" label Delete (you could of course, retain the 2 Grand Totals <g) and what you'll get remaining is essentially: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 There ... with some deft mouse control / touches, you should be able to create the PT in under 30 secs <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Stu-Gnu" wrote in message ... I have the following type of raw data: Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 I need to rearrange it so that 'A' and 'B' Unit Types appear in the same row, where the Member no. and the Date are the same, otherwise, they remain in seperate rows. The result should look as follows: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 Any suggestions as to the easiest way of doing this would be appreciated. |
|
#3
|
|||
|
|||
|
Max,
An additional couple of steps after you have produced the pivot table will give you the exact table you want: Select the entire pivot table, use Copy, then paste somewhere else as values. Then select just the first column of the new values table, and use Edit | Go To.. Special, Blanks, OK. Then type an = sign, hit the up arrow once, and press Ctrl-Enter. This will fill in the blank cells of the first column with the proper values. Then Copy and pastespecial values again to get rid of the formulas in that column, and you're done. HTH, Bernie MS Excel MVP "Max" wrote in message ... ... easiest way of doing this A Pivot Table would bring you very close in a matter of clicks & drags .. Assuming this data below is in Sheet1 Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 Click anywhere within the data Click Data Pivot Table Report Next Next In step 3 of the wizard: drag Member No and drop within ROW area drag Date and drop within ROW area (below Member No) drag Type and drop within COLUMN area drag Value and drop within DATA area (it'll appear as Sum of Value) Double-click on Member No Select None under Subtotals OK Click Finish The Pivot Table (PT) will appear in a new sheet to the left of Sheet1 In the PT: Right-click on the vertical "Grand Total" label Delete Right-click on the horiz "Grand Total" label Delete (you could of course, retain the 2 Grand Totals <g) and what you'll get remaining is essentially: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 There ... with some deft mouse control / touches, you should be able to create the PT in under 30 secs <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Stu-Gnu" wrote in message ... I have the following type of raw data: Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 I need to rearrange it so that 'A' and 'B' Unit Types appear in the same row, where the Member no. and the Date are the same, otherwise, they remain in seperate rows. The result should look as follows: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 Any suggestions as to the easiest way of doing this would be appreciated. |
|
#4
|
|||
|
|||
|
Thanks Max - I hadn't thought of using a PT. The only problem I have
encountered is that using Sum of Value in the DATA area shows zeros and i can't get it to show the appropriate values. "Max" wrote: ... easiest way of doing this A Pivot Table would bring you very close in a matter of clicks & drags .. Assuming this data below is in Sheet1 Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 Click anywhere within the data Click Data Pivot Table Report Next Next In step 3 of the wizard: drag Member No and drop within ROW area drag Date and drop within ROW area (below Member No) drag Type and drop within COLUMN area drag Value and drop within DATA area (it'll appear as Sum of Value) Double-click on Member No Select None under Subtotals OK Click Finish The Pivot Table (PT) will appear in a new sheet to the left of Sheet1 In the PT: Right-click on the vertical "Grand Total" label Delete Right-click on the horiz "Grand Total" label Delete (you could of course, retain the 2 Grand Totals <g) and what you'll get remaining is essentially: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 There ... with some deft mouse control / touches, you should be able to create the PT in under 30 secs <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Stu-Gnu" wrote in message ... I have the following type of raw data: Member No. Date Type Value 50772 28/02/2005 Unit A 480 50772 28/02/2005 Unit B 875 50772 02/03/2005 Unit A 109 66221 28/02/2005 Unit B 390 77344 28/02/2005 Unit A 133 77344 03/03/2005 Unit A 600 77344 03/03/2005 Unit B 82 I need to rearrange it so that 'A' and 'B' Unit Types appear in the same row, where the Member no. and the Date are the same, otherwise, they remain in seperate rows. The result should look as follows: Member No. Date Unit A Unit B 50772 28/02/2005 480 875 50772 02/03/2005 109 66221 28/02/2005 390 77344 28/02/2005 133 77344 03/03/2005 600 82 Any suggestions as to the easiest way of doing this would be appreciated. |
|
#5
|
|||
|
|||
|
Great add-on, Bernie ! Thanks ..
( .. with some practice, the exact table could still be up in under 30 secs! <g) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
|
#6
|
|||
|
|||
|
Stu,
Make sure you have actual numbers - no extra spaces, etc. - in those fields. HTH, Bernie MS Excel MVP "Stu-Gnu" wrote in message ... Thanks Max - I hadn't thought of using a PT. The only problem I have encountered is that using Sum of Value in the DATA area shows zeros and i can't get it to show the appropriate values. |
|
#7
|
|||
|
|||
|
"Stu-Gnu" wrote
Thanks Max - I hadn't thought of using a PT. You're welcome ! See Bernie's add-on steps to drive out the exact table The only problem I have encountered is that using Sum of Value in the DATA area shows zeros and i can't get it to show the appropriate values. Probably the numbers in source data under "Value" are text .. Try this on the source data in Sheet1 Copy an empty cell Select the Value col Right-click Paste special Add OK This should "coerce" the text numbers to real numbers Then go to the PT sheet: Right-click anywhere within the PT Refresh Data It should display correctly now -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| moving data in excel without deleting existing data | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
| Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
| Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions | |||
| Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) | |||
Linear Mode
