# Create one record for employee (Summary)

#### gycson

##### New Member
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.

You could try a pivot table such as below - but not sure if this is what you meant.
Book1
ABCDEFG
1NAMEGROSSPAYTYPEHRS.WKD.
2PeterNewton\$6,502.60Salary650.25
3PeterNewton\$1,700.00vac170.00
4PeterNewton\$1,500.00bonus200.00
5PeterNewton\$2,000.00ot100.00
6JohnJefferson\$6,486.00Salary540.50
7JohnJefferson\$1,080.00vac90.00
8JohnJefferson\$1,440.00ot1440.00
9
10
11
12PAYTYPE
13NAMEDatabonusotSalaryvacGrandTotal
14JohnJeffersonSumofGROSS1440648610809006
15SumofHRS.WKD.1440540.5902070.5
16PeterNewtonSumofGROSS150020006502.6170011702.6
17SumofHRS.WKD.200100650.251701120.25
18TotalSumofGROSS1500344012988.6278020708.6
19TotalSumofHRS.WKD.20015401190.752603190.75
Sheet1

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

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
ABCDEFGHI
1Yourdatalayout,however,numberedpaytypesopivottablewouldfollowyoursummarydesired
2NAMEGROSSPAYTYPEHRS.WKD.
3PeterNewton6,502.601-Salary650.25
4PeterNewton1,700.002-Vac170.00
5PeterNewton1,500.003-Bonus200.00
6PeterNewton2,000.004-OT100.00
7JohnJefferson6,486.001-Salary540.50
8JohnJefferson1,080.002-Vac90.00
9JohnJefferson1,440.004-OT1440.00
10
11Dataentrylayoutusingonly1lineperpayperiod,perperson
12-------PayPeriodJan.2006,Dollars------------PayPeriodJan.06,Hours-----
131-Salary2-Vac3-Bonus4-OT1-Salary2-Vac3-Bonus4-OT
14JohnJefferson6,486.001,080.000.001,440.00540.5090.000.001440.00
15PeterNewton6,502.601,700.001,500.002,000.00650.25170.00200.00100.00
16
17
18
19PivotTableCreatedusingyourdatalayout(top),notusinganytotalsonpivottableformattedTable10
20DataPAYTYPE
21GROSSHRS.WKD.
22NAME1-Salary2-Vac3-Bonus4-OT1-Salary2-Vac3-Bonus4-OT
23JohnJefferson6486.001080.000.001440.00540.5090.000.001440.00
24PeterNewton6502.601700.001500.002000.00650.25170.00200.00100.00
Sheet1

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

It works and did a summary too. Thanks you.

Replies
11
Views
1K
Replies
5
Views
4K

1,203,620
Messages
6,056,330
Members
444,861
Latest member
B4you_Andrea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back