Hi Chris

Mark your data range
Data>Pivot Table>Multiple Consolidation ranges>Next>I will create page
Fields>Next>Range> enter your range of data>Next>Finish

On the new page created with the Pivot Table, select the bottom right
cell Grand Total of Columns and Grand Total of Rows.
Double Click
A new page will be created with data set out as follows
Row Column Value
Bob Basic Pay 500
Bob Commission 300
Bob ID 2
Bob Overtime 50
James Basic Pay 400
James Commission
James ID 3
James Overtime 20
Sarah Basic Pay 500
Sarah Commission
Sarah ID 4
Sarah Overtime


Change Heading Row to Name, Column to Type
If you do need the ID numbers in a column, next to Name, then insert a
new column A and enter the following array formula in A2.
To enter an array formula (or modify it) commit with Ctrl+Shift+Enter
and Excel will insert the curly braces { } for you. Do not type them
yourself.
{=INDEX($B$2:$D$13,MATCH(B2&"ID",$B$2:$B$13&$C$2:$C$13,0),3)}
copy down through B3:B13.
Copy B2:B13, Paste Special>Values back over the formulae in B2:B13

If you don't then need the rows containing the ID, then,
Data>Filter>Autofilter>use dropdown on Type to Select ID.
Mark the range of visible rows by selecting their row numbers>Right
click>Delete.

Obviously you will have a lot more data, hence your ranges as above will
be expanded.
--
Regards

Roger Govier


"Chris" <Chris@discussions.microsoft.com> wrote in message
news:1582AA71-043B-4AD0-B945-0626DA5A70FA@microsoft.com...
> Hi,
>
> I am trying to convert the payroll data I receive from my clients into
> a csv
> file that I import in to my payroll program. Ideally I would like it
> to be
> all automatic (marco).
>
> The problem lies in the variable nature of the information I receive.
>
>
> Here is the basic layout:
>
> NAME | ID | Basic pay | Commission | Overtime
>
> Bob 2 500 300 50
> James 3 400 20
> Sarah 4 500
> And so forth
>
> THe layout I am trying to achieve is the following:
>
> Bob 2 Basic pay 500
> Bob 2 Commission 300
> Bob 2 Overtime 50
> James 3 Basic pay 400
> James 3 Overtime 40
> sarah 4 basic pay 500
> ...
> I can transpose the detail & amount easily but how can I get excel to
> automatically match the name and ID number for each field. As you can
> see Bob
> has 3 entries ,James has 2 and sarah only one. The number of employees
> varies
> every month and their payroll details also.
>
> Any help would be great.
>
> Thank You