Rows to columns and delete dups

Posted by Bill Adams on February 14, 2001 12:37 PM

Help !!!! I have spreadsheets where all employee information is repeated on a seperate row for each payroll account code which makes up his/her pay.

I would like to convert this to a single row of information for each employee. Everytime there is a new/different paroll account code a new column (with heading of the account code) and the amount paid should fall under each employee who has an entry for the code.

Suggestions ? (Be nice) ... hints ... help

Thanks in advance

I can also sent a workbook with before and after examples if this would help.

You can email me at bill.adams@cskcorp.com

Posted by Dave Hawley on February 14, 2001 12:53 PM

Hi Bill

Sounds like Subtotals may do the trick!

Make sure your columns have headings the click any cell in the table and go to Data>Subtotals.

If you get stuck, let me know.

Dave
OzGrid Business Applications

Posted by Bill Adams on February 14, 2001 1:38 PM

Personally "I" think the sub-totals is what they need but .... they don't.

Example

We have

CO SBU Pay Freq ---- EE# NAME YR PDBA TYP DBA NAME YTD EARN
00090 S014 S 10217 SMITH, AMY A 0 3 P SP REG PAY 41136.00
00090 S014 S 10217 SMITH, AMY A 0 64 P SP RETRO PAY 208.00
00090 S014 S 10217 SMITH, AMY A 0 76 P SP NO PAY 0.00
00090 S014 S 10217 SMITH, AMY A 0 223 P SP STK MATCH 463.72
00090 S014 S 10217 SMITH, AMY A 0 244 P SP GAINSHARE 4064.64
00090 S014 S 10217 SMITH, AMY A 0 7596 B SP CLUB DUES 183.60
00090 S026 S 10229 JONES, ANNA R 0 3 P SP REG PAY 27731.97
00090 S026 S 10229 JONES, ANNA R 0 64 P SP RETRO PAY 111.12
00090 S026 S 10229 JONES, ANNA R 0 76 P SP NO PAY 0.00
00090 S026 S 10229 JONES, ANNA R 0 118 P SP OT 1.5 1239.58
00090 S026 S 10229 JONES, ANNA R 0 244 P SP GAINSHARE 1601.62
00090 S103 S 10232 SMITH, ANNE E. 0 1 P SG REG PAY 41814.46
00090 S103 S 10232 SMITH, ANNE E. 0 62 P SG RETRO PAY 138.12
00090 S103 S 10232 SMITH, ANNE E. 0 74 P SG NO PAY 0.00
00090 S103 S 10232 SMITH, ANNE E. 0 221 P SG STK MATCH 360.36
00090 S103 S 10232 SMITH, ANNE E. 0 242 P SG GAINSHARE 3206.37
00090 S103 S 10232 SMITH, ANNE E. 0 5199 B LIFE INS(XS) 67.83
They want ...

CO SBU Pay Freq ---- EE# NAME DBA NAME sg reg pay ss reg pay sp reg pay sg retro pay ss retro pay sp retro pay sg ot 1.5 ss ot 1.5 sp ot 1.5 sg stk ma ss stk ma sp stk ma sg gainsh ss gainsh sp gainsh sg club d ss club d sp club d life ins (xs)
00090 S014 S 10217 SMITH, AMY A SP REG PAY 41136.00 208.00 463.72 4064.64 183.60
00090 S026 S 10229 JONES, ANNA R SP REG PAY 27731.97 111.12 1239.58 1601.62
00090 S103 S 10232 SMITH, ANNE E. SG REG PAY 41814.46 138.12 360.36 3206.37 67.83

41814.46 0.00 68867.97 138.12 0.00 319.12 0.00 0.00 1239.58 360.36 0.00 463.72 3206.37 0.00 5666.26 0.00 0.00 183.60 67.83

even though some columns are blank, as more ee records are added, they will have data.