create a summary record

[vcff]

Hi All

I need to gather some info regarding our vehicle maintenance.
I have a worksheet which include the following details under seven columns
and details will be keyed in when vehicle sent for servicing.

A1, B1, C1, D1, E1, F1, G1,
Workshop, Date of service, Lorry No., Invoice No., Invoice date, Amount,
Remark

I need to have two reports in another worksheet base on the abve info.
1. the list of workshops with the "total amount" charged under one month.

workshop Jan Feb Mar Apr .......


2. the list of vehicle with the "total amount" charged under the same month.

Lorry No. Jan Feb Mar Apr MAy .......

tnks in advance for the help

 

[vcff]

any help pls?

 

[Max]

Try a pivot table (PT). It'll get you the results you seek in a matter of
seconds

Select any cell within your source table.
Click Data > Pivot table ...
Click Next>Next.
In step 3, click Layout
Drag n drop Workshop within the Row area.
Drag n drop Invoice date within the Row area, below Workshop.
Drag n drop Amount within the Data area.
Click Ok > Finish

Go to the PT sheet
Right-click on any date in the Invoice date col > Group & show detail > Group
Accept the auto settings to group by "Months" > OK
Then just drag n drop "Invoice date" over "Total"
That's it. The pivot will return the desired results for (1)

And to get results for (2), just make a copy of the pivot sheet for (1),
then replace "Workshop" with "Lorry No."

In the copy of the pivot table sheet,
right-click on any cell in the PT > choose "Pivottable wizard"
Click Layout. Drag n drop "Workshop" out of the Row area (remove it). Then
just drag n drop "Lorry No." within the Row area (to replace Workshop). Click
OK > Finish. This would return the results you seek for (2).

 

[vcff]

Hi Max

Tnks for the help. It solved my problem.
As this is my first time using pivot table, I found that it is indeed a very
good program. However, may need more time to get used to it (slow learner).

Have A Nice Day

 

[Max]

Welcome. Thanks for feeding back.

 

[supoch14]

Hi All

I need to gather some info regarding our vehicle maintenance.
I have a worksheet which include the following details under seven columns
and details will be keyed in when vehicle sent for servicing.

A1, B1, C1, D1, E1, F1, G1,
Workshop, Date of service, Lorry No., Invoice No., Invoice date, Amount,
Remark

I need to have two reports in another worksheet base on the abve info.
1. the list of workshops with the "total amount" charged under one month.

workshop Jan Feb Mar Apr .......


2. the list of vehicle with the "total amount" charged under the same month.

Lorry No. Jan Feb Mar Apr MAy .......

tnks in advance for the help

 

[supoch14]

Try a pivot table (PT). It'll get you the results you seek in a matter of
seconds

Select any cell within your source table.
Click Data > Pivot table ...
Click Next>Next.
In step 3, click Layout
Drag n drop Workshop within the Row area.
Drag n drop Invoice date within the Row area, below Workshop.
Drag n drop Amount within the Data area.
Click Ok > Finish

Go to the PT sheet
Right-click on any date in the Invoice date col > Group & show detail > Group
Accept the auto settings to group by "Months" > OK
Then just drag n drop "Invoice date" over "Total"
That's it. The pivot will return the desired results for (1)

And to get results for (2), just make a copy of the pivot sheet for (1),
then replace "Workshop" with "Lorry No."

In the copy of the pivot table sheet,
right-click on any cell in the PT > choose "Pivottable wizard"
Click Layout. Drag n drop "Workshop" out of the Row area (remove it). Then
just drag n drop "Lorry No." within the Row area (to replace Workshop). Click
OK > Finish. This would return the results you seek for (2).