| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maybe by sumproduct or some other way with text.
hello again,
i may have been exagerating my work by having a large spreadsheet that rise up to 15MB. I can simplify my effort thru some formula from someone. my reference Table....can reach 20000 rows... e.g. A4:G2000 plan id rate name hrs amt start_date end_date 192 SC-001 $50 CCC 40 $2,000 1/22/2007 1/28/2007 192 SC-002 $70 BBB 40 $2,800 1/22/2007 1/28/2007 192 SC-003 $70 AAA 40 $2,800 1/22/2007 1/28/2007 191 SC-003 $70 AAA 40 $2,800 1/15/2007 1/21/2007 191 SC-002 $60 BBB 40 $2,400 1/15/2007 1/21/2007 191 SC-001 $50 CCC 40 $2,000 1/15/2007 1/21/2007 190 SC-002 $60 BBB 40 $2,400 1/8/2007 1/14/2007 190 SC-001 $40 CCC 40 $1,600 1/8/2007 1/14/2007 190 SC-003 $65 AAA 40 $2,600 1/8/2007 1/14/2007 189 SC-001 $40 CCC 40 $1,600 1/1/2007 1/7/2007 189 SC-002 $55 BBB 40 $2,200 1/1/2007 1/7/2007 189 SC-003 $60 AAA 40 $2,400 1/1/2007 1/7/2007 188 T-001 $30 DDD 40 $1,200 12/25/2006 12/31/2006 188 T-002 $40 EEE 40 $1,600 12/25/2006 12/31/2006 188 T-003 $50 FFF 40 $2,000 12/25/2006 12/31/2006 ...... ...... ..... on cell B1, i need a calculator formula that can produce the following text results. ----- Case 1) for past working personnel if i type on A1 the id number "T-001" the text result on B1 shall be something like this "Mr. DDD has worked for 40 hrs. with base hourly rate of $30 since Plan# 188 from 12/25/2006 until 12/31/2006." Case 2) for Currently working personnel if i type on A1 the id number "SC-001" the text result on B1 shall be something like this - "Mr. CCC has worked for 160 hrs. with base hourly rates of $40 since Plan# 189 from 1/1/2007, $50 since Plan# 191 from 1/15/2007 up to present." if i type on A1 the id number "SC-002" the text result on B1 shall be something like this - "Mr. BBB has worked for 160 hrs. with base hourly rate of $55 since Plan# 189 from 1/1/2007, $60 since Plan# 190 from 1/8/2007, $70 since Plan# 192 from 1/22/2007 up to present." if i type on A1 the id number "SC-003" the text result on B1 shall be something like this - "Mr. AAA has worked for 160 hrs. with base hourly rate of $60 since Plan# 189 from 1/1/2007, $65 since Plan# 190 from 1/8/2007, $70 since Plan# 191 from 1/15/2007 up to present." --- it may be better if the text result can include the total amount received per year. if required, the above can be expained farther. regards, driller -- ***** birds of the same feather flock together.. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maybe by sumproduct or some other way with text.
Hi
2 possible methods to get the required data. Method 1 Rearrange your columns to have Amt, ID, Name, Hrs, Rate, Plan, Start_date, End_date Apply Autofilter Use dropdown on ID to select person - data in columns C:H gives all required information in correct order, but Name is repeated each time. You could copy the data to another area, then delete the extra copies of Name to produce a nicer layout. Method 2 No need to rearrange data layout. Create a Pivot Table. Mark your range of data, DataPivot TableNextNextLayout Drag ID to Page area Drag the following fields to the Row area in this Order - Name, Hrs, Rate, Plan, Start_date, End_date Double click on each of the row fields in turn and set SubtotalsNoneOK Drag ID again to the Data area where it will become Count of ID Click OK, leave the Default location as new SheetFinish Hide column G if required (it is just a count of the jobs undertaken by that employee) From the Page field dropdown, select the ID required SC-003 for example and you will see a nicely formatted summary of his employment. Then rather than trying to use all the text that you are using, you could perhaps just have "Employment summary for Mr AAA" and below it paste a copy of the data from the PT. To copy from the PT, starting with the row above Total, mark the range of data you want to copy and use Ctrl C. (Whilst on the PT itself, right click does not give any opportunity to Copy.) Move to the area below your line of text and use Ctrl V to paste. -- Regards Roger Govier "driller" wrote in message ... hello again, i may have been exagerating my work by having a large spreadsheet that rise up to 15MB. I can simplify my effort thru some formula from someone. my reference Table....can reach 20000 rows... e.g. A4:G2000 plan id rate name hrs amt start_date end_date 192 SC-001 $50 CCC 40 $2,000 1/22/2007 1/28/2007 192 SC-002 $70 BBB 40 $2,800 1/22/2007 1/28/2007 192 SC-003 $70 AAA 40 $2,800 1/22/2007 1/28/2007 191 SC-003 $70 AAA 40 $2,800 1/15/2007 1/21/2007 191 SC-002 $60 BBB 40 $2,400 1/15/2007 1/21/2007 191 SC-001 $50 CCC 40 $2,000 1/15/2007 1/21/2007 190 SC-002 $60 BBB 40 $2,400 1/8/2007 1/14/2007 190 SC-001 $40 CCC 40 $1,600 1/8/2007 1/14/2007 190 SC-003 $65 AAA 40 $2,600 1/8/2007 1/14/2007 189 SC-001 $40 CCC 40 $1,600 1/1/2007 1/7/2007 189 SC-002 $55 BBB 40 $2,200 1/1/2007 1/7/2007 189 SC-003 $60 AAA 40 $2,400 1/1/2007 1/7/2007 188 T-001 $30 DDD 40 $1,200 12/25/2006 12/31/2006 188 T-002 $40 EEE 40 $1,600 12/25/2006 12/31/2006 188 T-003 $50 FFF 40 $2,000 12/25/2006 12/31/2006 ..... ..... .... on cell B1, i need a calculator formula that can produce the following text results. ----- Case 1) for past working personnel if i type on A1 the id number "T-001" the text result on B1 shall be something like this "Mr. DDD has worked for 40 hrs. with base hourly rate of $30 since Plan# 188 from 12/25/2006 until 12/31/2006." Case 2) for Currently working personnel if i type on A1 the id number "SC-001" the text result on B1 shall be something like this - "Mr. CCC has worked for 160 hrs. with base hourly rates of $40 since Plan# 189 from 1/1/2007, $50 since Plan# 191 from 1/15/2007 up to present." if i type on A1 the id number "SC-002" the text result on B1 shall be something like this - "Mr. BBB has worked for 160 hrs. with base hourly rate of $55 since Plan# 189 from 1/1/2007, $60 since Plan# 190 from 1/8/2007, $70 since Plan# 192 from 1/22/2007 up to present." if i type on A1 the id number "SC-003" the text result on B1 shall be something like this - "Mr. AAA has worked for 160 hrs. with base hourly rate of $60 since Plan# 189 from 1/1/2007, $65 since Plan# 190 from 1/8/2007, $70 since Plan# 191 from 1/15/2007 up to present." --- it may be better if the text result can include the total amount received per year. if required, the above can be expained farther. regards, driller -- ***** birds of the same feather flock together.. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maybe by sumproduct or some other way with text.
thanks mr. roger,
the active employees may grow to 300, meaning the weekly data sheet may grow 1200 rows a month. And to copy-paste-print may take more time to process each employees record as individual report in a month. Maybe a farther elaboration on how PT work with the 2nd option is to know how to pre-set a continuous printing event based on selected list of employees only. thanks and regards, driller -- ***** birds of the same feather flock together.. "Roger Govier" wrote: Hi 2 possible methods to get the required data. Method 1 Rearrange your columns to have Amt, ID, Name, Hrs, Rate, Plan, Start_date, End_date Apply Autofilter Use dropdown on ID to select person - data in columns C:H gives all required information in correct order, but Name is repeated each time. You could copy the data to another area, then delete the extra copies of Name to produce a nicer layout. Method 2 No need to rearrange data layout. Create a Pivot Table. Mark your range of data, DataPivot TableNextNextLayout Drag ID to Page area Drag the following fields to the Row area in this Order - Name, Hrs, Rate, Plan, Start_date, End_date Double click on each of the row fields in turn and set SubtotalsNoneOK Drag ID again to the Data area where it will become Count of ID Click OK, leave the Default location as new SheetFinish Hide column G if required (it is just a count of the jobs undertaken by that employee) From the Page field dropdown, select the ID required SC-003 for example and you will see a nicely formatted summary of his employment. Then rather than trying to use all the text that you are using, you could perhaps just have "Employment summary for Mr AAA" and below it paste a copy of the data from the PT. To copy from the PT, starting with the row above Total, mark the range of data you want to copy and use Ctrl C. (Whilst on the PT itself, right click does not give any opportunity to Copy.) Move to the area below your line of text and use Ctrl V to paste. -- Regards Roger Govier "driller" wrote in message ... hello again, i may have been exagerating my work by having a large spreadsheet that rise up to 15MB. I can simplify my effort thru some formula from someone. my reference Table....can reach 20000 rows... e.g. A4:G2000 plan id rate name hrs amt start_date end_date 192 SC-001 $50 CCC 40 $2,000 1/22/2007 1/28/2007 192 SC-002 $70 BBB 40 $2,800 1/22/2007 1/28/2007 192 SC-003 $70 AAA 40 $2,800 1/22/2007 1/28/2007 191 SC-003 $70 AAA 40 $2,800 1/15/2007 1/21/2007 191 SC-002 $60 BBB 40 $2,400 1/15/2007 1/21/2007 191 SC-001 $50 CCC 40 $2,000 1/15/2007 1/21/2007 190 SC-002 $60 BBB 40 $2,400 1/8/2007 1/14/2007 190 SC-001 $40 CCC 40 $1,600 1/8/2007 1/14/2007 190 SC-003 $65 AAA 40 $2,600 1/8/2007 1/14/2007 189 SC-001 $40 CCC 40 $1,600 1/1/2007 1/7/2007 189 SC-002 $55 BBB 40 $2,200 1/1/2007 1/7/2007 189 SC-003 $60 AAA 40 $2,400 1/1/2007 1/7/2007 188 T-001 $30 DDD 40 $1,200 12/25/2006 12/31/2006 188 T-002 $40 EEE 40 $1,600 12/25/2006 12/31/2006 188 T-003 $50 FFF 40 $2,000 12/25/2006 12/31/2006 ..... ..... .... on cell B1, i need a calculator formula that can produce the following text results. ----- Case 1) for past working personnel if i type on A1 the id number "T-001" the text result on B1 shall be something like this "Mr. DDD has worked for 40 hrs. with base hourly rate of $30 since Plan# 188 from 12/25/2006 until 12/31/2006." Case 2) for Currently working personnel if i type on A1 the id number "SC-001" the text result on B1 shall be something like this - "Mr. CCC has worked for 160 hrs. with base hourly rates of $40 since Plan# 189 from 1/1/2007, $50 since Plan# 191 from 1/15/2007 up to present." if i type on A1 the id number "SC-002" the text result on B1 shall be something like this - "Mr. BBB has worked for 160 hrs. with base hourly rate of $55 since Plan# 189 from 1/1/2007, $60 since Plan# 190 from 1/8/2007, $70 since Plan# 192 from 1/22/2007 up to present." if i type on A1 the id number "SC-003" the text result on B1 shall be something like this - "Mr. AAA has worked for 160 hrs. with base hourly rate of $60 since Plan# 189 from 1/1/2007, $65 since Plan# 190 from 1/8/2007, $70 since Plan# 191 from 1/15/2007 up to present." --- it may be better if the text result can include the total amount received per year. if required, the above can be expained farther. regards, driller -- ***** birds of the same feather flock together.. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maybe by sumproduct or some other way with text.
the pivot table was now set as per suggestion. Looks neat. I may just post
for another question on how to automate printing of selected *only* employees record...considering that the page field cannot accept a formula - other than a typical dropdown list. thanks a lot and more power, driller -- ***** birds of the same feather flock together.. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maybe by sumproduct or some other way with text.
Hi
Maybe you can modify the following macro to achieve what you want. Create a selection of the ID's you want to print, in my case here it is located on Sheet "List" in cells K1:K3. This can be achieved with Autofilter from your main data, with copy and paste to another location. Then run the following macro Sub PrintPivotPages() Dim myRange As Range Dim myCell As Range Set myRange = Sheets("List").Range("K1:K3") For Each myCell In myRange 'rem change sheet name and pivot name to suit Sheets("Pivot").PivotTables("PivotTable1").PivotFi elds("id").CurrentPage = myCell.Text Sheets("Pivot").Range("A3:F20").Print Next End Sub My sheet with the Pivot table was called Pivot, change to suit your situation, and also change the name of the Pivot Table if necessary. If you right click on your Pivot table, choose Table Options and you will see the name of your table. -- Regards Roger Govier "driller" wrote in message ... the pivot table was now set as per suggestion. Looks neat. I may just post for another question on how to automate printing of selected *only* employees record...considering that the page field cannot accept a formula - other than a typical dropdown list. thanks a lot and more power, driller -- ***** birds of the same feather flock together.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT but with text containing | Excel Worksheet Functions | |||
| help - sumproduct within text range- | Excel Discussion (Misc queries) | |||
| Is there a SUMPRODUCT-like function that I can use on text? | Excel Worksheet Functions | |||
| Sumproduct Text | Excel Discussion (Misc queries) | |||
| SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Linear Mode
