|
Help needed with a calculation question
Ok received alot of help here before hoping you can help again.
Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
CP,
For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
Ok that seemed to take me in the right direction but then led me to further
problems. Good insite into pivot tables though. When I made the pivot table it seemed only to want to take the first M value and wanted to ignore the rest, I was maybe thinking there would be a vlookup to do this, another problem is that there is other unorganised data in the table/columns merged and such (this is data I get from elswhere). I was hoping to import the data into sheet 1 each month and the other sheets pick out the right data - considering the input data does not change in columns.?? "Bernie Deitrick" wrote: CP, For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
Put a list of your Acc codes down column A of your summany sheet, and use a formula like
=SUMIF(DataSheet!A:A,$A2,DataSheet!B:B) and copy down and accross to make a table. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok that seemed to take me in the right direction but then led me to further problems. Good insite into pivot tables though. When I made the pivot table it seemed only to want to take the first M value and wanted to ignore the rest, I was maybe thinking there would be a vlookup to do this, another problem is that there is other unorganised data in the table/columns merged and such (this is data I get from elswhere). I was hoping to import the data into sheet 1 each month and the other sheets pick out the right data - considering the input data does not change in columns.?? "Bernie Deitrick" wrote: CP, For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
Ooops. To copy across correctly, you need
=SUMIF(DataSheet!$A:$A,$A2,DataSheet!B:B) Sorry about that.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Put a list of your Acc codes down column A of your summany sheet, and use a formula like =SUMIF(DataSheet!A:A,$A2,DataSheet!B:B) and copy down and accross to make a table. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok that seemed to take me in the right direction but then led me to further problems. Good insite into pivot tables though. When I made the pivot table it seemed only to want to take the first M value and wanted to ignore the rest, I was maybe thinking there would be a vlookup to do this, another problem is that there is other unorganised data in the table/columns merged and such (this is data I get from elswhere). I was hoping to import the data into sheet 1 each month and the other sheets pick out the right data - considering the input data does not change in columns.?? "Bernie Deitrick" wrote: CP, For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
Ok I think I am going to have to sort my data properley - remove merged
sections and headers as it still doesnt work but can I just ask the following DataSheet!$A:$A = the whole of column A in the orignal data $A2 = the matching section on the new sheet to match the original data DataSheet!B:B = the source of data, ie the values to place on the new sheet There fore after copying all my Acc numbers into Column A sheet2 I would place this in the first row of the summary sheet in column B =SUMIF(Sheet1!$A:$A,$A1,Sheet1!B:B) ?? "Bernie Deitrick" wrote: Ooops. To copy across correctly, you need =SUMIF(DataSheet!$A:$A,$A2,DataSheet!B:B) Sorry about that.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Put a list of your Acc codes down column A of your summany sheet, and use a formula like =SUMIF(DataSheet!A:A,$A2,DataSheet!B:B) and copy down and accross to make a table. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok that seemed to take me in the right direction but then led me to further problems. Good insite into pivot tables though. When I made the pivot table it seemed only to want to take the first M value and wanted to ignore the rest, I was maybe thinking there would be a vlookup to do this, another problem is that there is other unorganised data in the table/columns merged and such (this is data I get from elswhere). I was hoping to import the data into sheet 1 each month and the other sheets pick out the right data - considering the input data does not change in columns.?? "Bernie Deitrick" wrote: CP, For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
Help needed with a calculation question
If the data sheet is named "Sheet1", and cell A1 has a valid Acc code, then yes... Note that you
should have a list of unique values in column A of Sheet2, not simply a copied list from Sheet1. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok I think I am going to have to sort my data properley - remove merged sections and headers as it still doesnt work but can I just ask the following DataSheet!$A:$A = the whole of column A in the orignal data $A2 = the matching section on the new sheet to match the original data DataSheet!B:B = the source of data, ie the values to place on the new sheet There fore after copying all my Acc numbers into Column A sheet2 I would place this in the first row of the summary sheet in column B =SUMIF(Sheet1!$A:$A,$A1,Sheet1!B:B) ?? "Bernie Deitrick" wrote: Ooops. To copy across correctly, you need =SUMIF(DataSheet!$A:$A,$A2,DataSheet!B:B) Sorry about that.... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Put a list of your Acc codes down column A of your summany sheet, and use a formula like =SUMIF(DataSheet!A:A,$A2,DataSheet!B:B) and copy down and accross to make a table. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok that seemed to take me in the right direction but then led me to further problems. Good insite into pivot tables though. When I made the pivot table it seemed only to want to take the first M value and wanted to ignore the rest, I was maybe thinking there would be a vlookup to do this, another problem is that there is other unorganised data in the table/columns merged and such (this is data I get from elswhere). I was hoping to import the data into sheet 1 each month and the other sheets pick out the right data - considering the input data does not change in columns.?? "Bernie Deitrick" wrote: CP, For the first one, use Data / Pivot table / and use Acc as a row field, and M1, M2, and M3 as data fields. For your second question, use Data / Filter.. Autofilter, and use the drop down on the Type column to select your R, G, H, etc.. DON'T move the data to extra sheets. That is a data disaster waiting to happen. HTH, Bernie MS Excel MVP "CP" wrote in message ... Ok received alot of help here before hoping you can help again. Table below showing my data on sheet1 Acc M1 M2 M3 Type 406 100 100 0 R 406 300 200 50 G 406 0 0 10 H 506 30 800 0 R 506 0 0 300 G Ok I first need all totals of columns M1,M2,M3 where Acc is equel (assuming this will done on another sheet) So you would get - 406 400 300 60 506 30 800 300 Secondly (on again another sheet the total) I need to move all the "R" rows to another sheet - I would then do the same for H,G. So the R sheet would become 406 100 100 0 506 30 800 0 I do have an understanding of naming rows if that would ease things. I hope this makes sense. Thanks in advance for any help |
| All times are GMT +1. The time now is 05:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com