Moving selected data

[Stu-Gnu]

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.

 

[Max]

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

 

[Bernie Deitrick]

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

 

[Stu-Gnu]

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]

Great add-on, Bernie ! Thanks ..
( .. with some practice, the exact table could still be up in under 30 secs!
<g>)

 

[Bernie Deitrick]

Stu,

Make sure you have actual numbers - no extra spaces, etc. - in those
fields.

HTH,
Bernie
MS Excel MVP

 

[Max]

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