MrExcel.com - Your One Stop for Excel Tips & Solutions

Convert a Detail Workbook into a Summary Report

Posted by Matteo Dobrini on September 07, 2000 10:52 AM

I would like each employee in a single line with the total DAYS taken year to date. I know about subtotals but that doesn't bring the other data down into the subtotal line which I need. I don't need the FROM DATE OR TO DATE OR CODE. There are over 4800 lines- way too much for deleting the lines I don't need...

Thanks..

NUMB LAST NAME FI FROM DATE TO DATE CODE DAYS ENT HIRE DATE
79706 LINTON C 03/23/2000 03/23/2000 03 1.00 17 12/01/1988
79706 LINTON C 05/15/2000 05/15/2000 03 1.00 17 12/01/1988
79706 LINTON C 06/19/2000 06/19/2000 03 1.00 17 12/01/1988
79706 LINTON C 06/23/2000 06/23/2000 03 1.00 17 12/01/1988
79706 LINTON C 07/28/2000 07/28/2000 03 1.00 17 12/01/1988
79706 LINTON C 02/16/2000 02/16/2000 14 1.00 17 12/01/1988
00027 DIAMOND D 01/07/2000 01/14/2000 03 6.00 22 02/11/1980
00027 DIAMOND D 02/22/2000 02/22/2000 03 1.00 22 02/11/1980
00027 DIAMOND D 04/21/2000 04/24/2000 03 2.00 22 02/11/1980
00027 DIAMOND D 05/12/2000 05/12/2000 03 1.00 22 02/11/1980
00027 DIAMOND D 06/27/2000 06/27/2000 03 1.00 22 02/11/1980
00027 DIAMOND D 07/03/2000 07/03/2000 03 1.00 22 02/11/1980
00027 DIAMOND D 08/21/2000 08/25/2000 03 5.00 22 02/11/1980
00027 DIAMOND D 01/06/2000 01/06/2000 14 1.00 22 02/11/1980

Posted by Celia on September 07, 0100 11:34 PM

Matteo
Here's one method.
It is assumed that your headings start in A1.
1.Select all columns with data (columns A:M in your sample)
2.Go to Data>SubTotals and select :- Each Change In "NUMB", Use Function "Sum", Add Sub-total To "DAYS"(make sure no other items are also checked).Click OK.
3.Select A1:M17(I.E.down to the row above the grand-total row), with A1 as the active cell.
4.Go to Edit>GoTo>Special>Blanks and click OK.
5.Go to Format>Cells>Number>General and click OK.
6.The active cell should be B8 (and all the blank cells should still be selected). Type into B8 the formula =B7 and press Ctrl+Enter. This should fill each blank cell with the data in the cell immediately above.
7.Click on the sub-total collapse/expand button 2.
8.Hide columns D:F if the information is not needed.

That's it (I hope!).
If you want to copy the sub-totals to somewhere else, make sure to copy the values only,

Celia