Create one record for employee (Summary)

[gycson]

I have a big table (560 payroll employees) with repeated column information that I need to summary in one record per employee. Example per period:

Name.....................Gross............Pay Type................Hours Worked

Peter Newton..........6,502.50.........Salary......................650.25
Peter Newton..........1,700.00.........Vacations..................170.00
Peter Newton..........1,500.00.........Bonus.......................200.00
Peter Newton..........2,000.00.........Overtime..................100.00
John Jefferson.........6,486.00........Salary.......................540.50
John Jefferson.........1,080.00........Vacations....................90.00
John Jefferson.........1,440.00........Overtime....................60.00
etc..

Note not all employees has the same pay type. I want to create one record per employee (summary) with columns for pay type. It is posible that employee has more data in other period. I trying to use VLOOKUP, but not work.

Thanks you for your cooperation.

 

[Peter_SSs]

gycson

Welcome to the Mr Excel board!

You have given us a sample of your original data. Could you also give us a sample of your required results for the original sample of data already provided.

 

[jdee]

You could try a pivot table such as below - but not sure if this is what you meant.

Book1
	A	B	C	D	E	F	G
1	NAME	GROSS	PAYTYPE	HRS.WKD.
2	PeterNewton	$6,502.60	Salary	650.25
3	PeterNewton	$1,700.00	vac	170.00
4	PeterNewton	$1,500.00	bonus	200.00
5	PeterNewton	$2,000.00	ot	100.00
6	JohnJefferson	$6,486.00	Salary	540.50
7	JohnJefferson	$1,080.00	vac	90.00
8	JohnJefferson	$1,440.00	ot	1440.00
9
10
11
12			PAYTYPE
13	NAME	Data	bonus	ot	Salary	vac	GrandTotal
14	JohnJefferson	SumofGROSS		1440	6486	1080	9006
15		SumofHRS.WKD.		1440	540.5	90	2070.5
16	PeterNewton	SumofGROSS	1500	2000	6502.6	1700	11702.6
17		SumofHRS.WKD.	200	100	650.25	170	1120.25
18	TotalSumofGROSS		1500	3440	12988.6	2780	20708.6
19	TotalSumofHRS.WKD.		200	1540	1190.75	260	3190.75
Sheet1

 

[gycson]

I want to create one employee record per row.

NAME..................GROSS.....PAY TYPE...HRS. WKD.
Peter Newton...... $6,502.60.....Salary....650.25
Peter Newton.......$1,700.00.....vac........170.00
Peter Newton.......$1,500.00.....bonus.....200.00
Peter Newton.......$2,000.00.....ot...........100.00
John Jefferson......$6,486.00.....Salary....540.50
John Jefferson......$1,080.00.....vac..........90.00
John Jefferson......$1,440.00.....ot.........1440.00

The results will looks like these:

Peter Newton.....$6,502.60....$1,700.00....$1,500.00.....$2,000.00....650.25.....170.00....200.00.....100.00
John Jefferson...same as the above.
Thanks you

Gycson

 

[jdee]

Gycson:
I'm making few assumptions here re: your data input, etc. First, if you are entering data as you show, I would suggest you may want to data data in one row per pay period, per person.

I did create a pivot table using your data layout, with exception of numbering the pay types, otherwise they showed up in alpha. order on pivot table and numbering worked. I then formatted the pivot table to your needs, i.e., all salary dollars, then hours. Examples of your data, how data could be entered, and pivot table below.

PIVOT QUESTION FROM MREXCEL.xls
	A	B	C	D	E	F	G	H	I
1	Yourdatalayout,however,numberedpaytypesopivottablewouldfollowyoursummarydesired
2	NAME	GROSS	PAYTYPE	HRS.WKD.
3	PeterNewton	6,502.60	1-Salary	650.25
4	PeterNewton	1,700.00	2-Vac	170.00
5	PeterNewton	1,500.00	3-Bonus	200.00
6	PeterNewton	2,000.00	4-OT	100.00
7	JohnJefferson	6,486.00	1-Salary	540.50
8	JohnJefferson	1,080.00	2-Vac	90.00
9	JohnJefferson	1,440.00	4-OT	1440.00
10
11	Dataentrylayoutusingonly1lineperpayperiod,perperson
12		-------PayPeriodJan.2006,Dollars-------				-----PayPeriodJan.06,Hours-----
13		1-Salary	2-Vac	3-Bonus	4-OT	1-Salary	2-Vac	3-Bonus	4-OT
14	JohnJefferson	6,486.00	1,080.00	0.00	1,440.00	540.50	90.00	0.00	1440.00
15	PeterNewton	6,502.60	1,700.00	1,500.00	2,000.00	650.25	170.00	200.00	100.00
16
17
18
19	PivotTableCreatedusingyourdatalayout(top),notusinganytotalsonpivottableformattedTable10
20		Data	PAYTYPE
21		GROSS				HRS.WKD.
22	NAME	1-Salary	2-Vac	3-Bonus	4-OT	1-Salary	2-Vac	3-Bonus	4-OT
23	JohnJefferson	6486.00	1080.00	0.00	1440.00	540.50	90.00	0.00	1440.00
24	PeterNewton	6502.60	1700.00	1500.00	2000.00	650.25	170.00	200.00	100.00
Sheet1

Hopefully, this is what you mean. Feel free to contact me if you should need further help. J.Dee

 

[gycson]

It works and did a summary too. Thanks you.