Analysing data through pivot

[Guest]

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

 

[Guest]

Hi Chris,

It looks as though you're trying to use Excel as a relational database and
it's not designed for that. Do you have Access by any chance? That would
really be the way to go.

 

[Guest]

Yes I was afraid of that.

The client doesn't use access so I was trying to keep everything on excel.

However, can I set an automated process like a macro to perform this task
through access and still end up with a csv file?

I use access & excel but always seperatly so I don't know if they can
interact with each other in a suitable way for this task.

 

[Roger Govier]

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.