If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data in list w/same A:B move to column
Hi, I have a list of source data that I need to change to format of and I am not sure how to do it. Maybe a pivot table? What I want to do is have each Pers. No. and name displayed in only one row. Then I want the other date & date type pairs displayed in the columns next to Hire date. So each employee will have multiple columns of dates and date types. Does anyone know how to do this? I tried a pivot table but I dont want to do any calculations with the data... I just want to display it and I cant find a way to get Excel to do that. I would greatly appreciate anyones help! Thanks, Adrianne Pers.No. Personnel Number Date Date type 5 Hopkins , Kevin 11/17/1998 Hire Date 5 Hopkins , Kevin 11/17/1998 Adj Service Date 5 Hopkins , Kevin 01/19/1999 Rehire Date 5 Hopkins , Kevin 01/19/1999 Vac Accrual Date 7 Zinis , Anthony F 04/18/2000 Hire Date 7 Zinis , Anthony F 04/18/2000 Adj Service Date 7 Zinis , Anthony F 04/18/2000 Vac Accrual Date 9 McTyre , Mark 01/24/2000 Hire Date 9 McTyre , Mark 01/24/2000 Adj Service Date -- Adresmith ------------------------------------------------------------------------ Adresmith's Profile: http://www.excelforum.com/member.php...fo&userid=9131 View this thread: http://www.excelforum.com/showthread...hreadid=504582 |
#2
|
|||
|
|||
Data in list w/same A:B move to column
Adrianne -
If each Date Type can have only one entry per person (i.e., someone can't have two Rehire Dates), you can use a pivot table. Follow the pivot table wizard and Drag Pers.No to the left most column (set field settings subtotal to none) Drag Name to the column area adjacent to Pers No Drag Date Type to the top Column area Drag Date to the data area. This will likely default to a count. Change it to sum and change the format to date. Under Table Options, turn off grand totals for rows and columns This should get you going. As mentioned it only works if there is a max of one entry per date type. If there is two, then the date shows in the table will be significantly off...the year would be in another century. - John Michl www.JohnMichl.com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return SEARCHED Column Number of Numeric Label and Value | Sam via OfficeKB.com | Worksheet Functions | 23 | January 30th, 2006 06:16 PM |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
Sort pages? | David | General Discussion | 15 | May 13th, 2005 11:33 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |