Consolidate report in Different sheet

[Krishna Kumar L]

Hi,

I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance.

Data collection sheet

Date Mode Route Prodcuct1 Product2 Product3 Total cash Total
1-2-09 Sales Line 1 10 10 10 Rs.3000 30
1-2-09 Sales line2 5 20 Rs.4000 15
1-2-09 return line1 5 10 -Rs.3000 -15
2-2-09 Sales Line3 10 10 10 Rs.5000 30
2-2-09 Sales Line4 50 Rs.6000 50


Consolidation sheet

Filter all columns

Date mode route product1 product2 product3 Total cash total products
1-2-09 In/out line1 10 30 0 Rs.3000 40
2-2-09 In/Out Line3 60 10 Rs.6000 70

All prices are just examples... kindly use filters and help me

Thanks

KK

 

[Roger Govier]

Hi

You will need to enter - values in the the return items.
Then Data>Subtotal>At each change in>Date>Use function>Sum>Tick all of the boxes>OK

Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want.
Note the result is different from what you show
Day 1 cash is 4000
Day 2 cash is 11000


--
Regards
Roger Govier
Hi,

I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance.

Data collection sheet

Date Mode Route Prodcuct1 Product2 Product3 Total cash Total
1-2-09 Sales Line 1 10 10 10 Rs.3000 30
1-2-09 Sales line2 5 20 Rs.4000 15
1-2-09 return line1 5 10 -Rs.3000 -15
2-2-09 Sales Line3 10 10 10 Rs.5000 30
2-2-09 Sales Line4 50 Rs.6000 50


Consolidation sheet

Filter all columns

Date mode route product1 product2 product3 Total cash total products
1-2-09 In/out line1 10 30 0 Rs.3000 40
2-2-09 In/Out Line3 60 10 Rs.6000 70

All prices are just examples... kindly use filters and help me

Thanks

KK

 

[Krishna Kumar L]

Hi,

I am unable to understand your solution. I need all details to that date to reflect in the new sheet. This way I can find out all the products I sold on that day and also the route/line I sold the same. Another thing I will get is the amount of outward and Inward. Outward and Inward is sales and sales return. Every month I will be using a new sheet.

Thank you

KK
Hi

You will need to enter - values in the the return items.
Then Data>Subtotal>At each change in>Date>Use function>Sum>Tick all of the boxes>OK

Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want.
Note the result is different from what you show
Day 1 cash is 4000
Day 2 cash is 11000


--
Regards
Roger Govier
Hi,

I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance.

Data collection sheet

Date Mode Route Prodcuct1 Product2 Product3 Total cash Total
1-2-09 Sales Line 1 10 10 10 Rs.3000 30
1-2-09 Sales line2 5 20 Rs.4000 15
1-2-09 return line1 5 10 -Rs.3000 -15
2-2-09 Sales Line3 10 10 10 Rs.5000 30
2-2-09 Sales Line4 50 Rs.6000 50


Consolidation sheet

Filter all columns

Date mode route product1 product2 product3 Total cash total products
1-2-09 In/out line1 10 30 0 Rs.3000 40
2-2-09 In/Out Line3 60 10 Rs.6000 70

All prices are just examples... kindly use filters and help me

Thanks

KK

 

[Roger Govier]

Hi

My suggestion does not put the data on another sheet.
It summarises the data by day, giving the Totals that you want for that day, and "hiding" all the detail lines.
Clicking the "+" button next to any day, will expose all of the lines making up that Sub total, and the icon will change to a "-".
Clicking the "-" will hide all the detail again and just show you a single line total for the day.

As I said in my first posting, you will have to ensure that all entries on the Return rows are entered as Negative values.

I will email a small sample file to you.

--
Regards
Roger Govier
Hi,

I am unable to understand your solution. I need all details to that date to reflect in the new sheet. This way I can find out all the products I sold on that day and also the route/line I sold the same. Another thing I will get is the amount of outward and Inward. Outward and Inward is sales and sales return. Every month I will be using a new sheet.

Thank you

KK
Hi

You will need to enter - values in the the return items.
Then Data>Subtotal>At each change in>Date>Use function>Sum>Tick all of the boxes>OK

Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want.
Note the result is different from what you show
Day 1 cash is 4000
Day 2 cash is 11000


--
Regards
Roger Govier
Hi,

I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance.

Data collection sheet

Date Mode Route Prodcuct1 Product2 Product3 Total cash Total
1-2-09 Sales Line 1 10 10 10 Rs.3000 30
1-2-09 Sales line2 5 20 Rs.4000 15
1-2-09 return line1 5 10 -Rs.3000 -15
2-2-09 Sales Line3 10 10 10 Rs.5000 30
2-2-09 Sales Line4 50 Rs.6000 50


Consolidation sheet

Filter all columns

Date mode route product1 product2 product3 Total cash total products
1-2-09 In/out line1 10 30 0 Rs.3000 40
2-2-09 In/Out Line3 60 10 Rs.6000 70

All prices are just examples... kindly use filters and help me

Thanks

KK