maybe by sumproduct or some other way with text.

[driller]

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

 

[Roger Govier]

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, Data>Pivot Table>Next>Next>Layout
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 Subtotals>None>OK
Drag ID again to the Data area where it will become Count of ID
Click OK, leave the Default location as new Sheet>Finish

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.

 

[driller]

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..

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, Data>Pivot Table>Next>Next>Layout
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 Subtotals>None>OK
Drag ID again to the Data area where it will become Count of ID
Click OK, leave the Default location as new Sheet>Finish

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.

 

[driller]

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

 

[Roger Govier]

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").PivotFields("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.