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.

> ... 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 <at>yahoo<dot>com
----
"Stu-Gnu" <Stu-Gnu@discussions.microsoft.com> wrote in message
news:7AB78548-583E-47BB-AE64-BC1A11219555@microsoft.com...
> 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,

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" <demechanik@yahoo.com> wrote in message
news:ONtoK9jJFHA.1948@TK2MSFTNGP14.phx.gbl...
> > ... 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 <at>yahoo<dot>com
> ----
> "Stu-Gnu" <Stu-Gnu@discussions.microsoft.com> wrote in message
> news:7AB78548-583E-47BB-AE64-BC1A11219555@microsoft.com...
> > 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.
>
>

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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
> ----
> "Stu-Gnu" <Stu-Gnu@discussions.microsoft.com> wrote in message
> news:7AB78548-583E-47BB-AE64-BC1A11219555@microsoft.com...
> > 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.
>
>
>

Stu,

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

HTH,
Bernie
MS Excel MVP

"Stu-Gnu" <StuGnu@discussions.microsoft.com> wrote in message
news:8C669EE2-120C-4856-8BBC-041122BE25FC@microsoft.com...
> 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.

"Stu-Gnu" <StuGnu@discussions.microsoft.com> 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 <at>yahoo<dot>com
----