Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
Morning, I have a column of data (numbers) which extend down between
rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
REally you need to copy the staff member names down so your table looks more
like this Staff 1 Staff 1 data number Total Staff 1 data number Staff 2 Staff 2 data number Total Staff 2 data number Staff 2 data number Staff 2 data number Staff 3 Staff 3 data number Total Staff 3 data number Staff 3 data number After that getting the subtotals is not that terribley difficult. Yu could use subtotaling, pivot tables or even array formulas. Not sure which would be your favorite so I will let you decide that. To poplulate the first column do this Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now select all of row a which contains the staff members. A4 - A1000. Hit F5. Click on the Special Cells Button. Check off Blank cells. All of the blank cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first column should now be filled in. Copy and paste special values so that your formulas become hard coded values and you should be off to the races... HTH " wrote: Morning, I have a column of data (numbers) which extend down between rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
Jim Thanks for the information, but I do not have the luxury of making
changes to the base format..... will be used by State & Feds. So I can't repeat names. I am designing only the totalling function for column 7 [Totals]. Here is real data information to give you a better idea. Name Type Number Value Units Totals ABROMS, G. NM16 3 EMPLOYER RESPONSE 5 15 x AXEL, R. CT14 74 RETURN TO WORK 5 370 x ML06 5 MASS LAYOFF ISSUE 3 15 NM02 1 ABILITY 9 9 NM06 5 AVAILABILITY 8 40 NM16 1 EMPLOYER RESPONSE 5 5 NM22 1 FIRED 43.6 43.6 NM42 2 REMUNERATION 12 24 NM58 3 SUFFICIENT WORK WAGE 5 15 ARMSTRONG, R. NM02 2 ABILITY 9 18 X NM06 4 AVAILABILITY 8 32 NM10 4 BASE PERIOD WAGES 12 48 NM16 14 EMPLOYER RESPONSE 5 70 NM22 108 FIRED 43.6 4708.8 NM40 59 QUIT 40 2360 NM42 2 REMUNERATION 12 24 NM46 1 SUITABLE WORK 15 15 NM56 18 VALID CLAIM 13 234 BROWN, B NM02 4 ABILITY 9 36 X NM06 9 AVAILABILITY 8 72 NM42 1 REMUNERATION 12 12 NM56 5 VALID CLAIM 13 65 NX04 1 INELIGIBILITY REVIEW 15 15 As you can see the number and type of cases varies, so I need to devise a programming or formula which totals the units column values for each staff member. Thanks Though. Pete Jim Thomlinson wrote: REally you need to copy the staff member names down so your table looks more like this Staff 1 Staff 1 data number Total Staff 1 data number Staff 2 Staff 2 data number Total Staff 2 data number Staff 2 data number Staff 2 data number Staff 3 Staff 3 data number Total Staff 3 data number Staff 3 data number After that getting the subtotals is not that terribley difficult. Yu could use subtotaling, pivot tables or even array formulas. Not sure which would be your favorite so I will let you decide that. To poplulate the first column do this Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now select all of row a which contains the staff members. A4 - A1000. Hit F5. Click on the Special Cells Button. Check off Blank cells. All of the blank cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first column should now be filled in. Copy and paste special values so that your formulas become hard coded values and you should be off to the races... HTH " wrote: Morning, I have a column of data (numbers) which extend down between rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
Based on that you are probably looking for a custom programming kind of a
function. Let me take a look at it and see what I can do for you. This will not be something that you will easily be able to modify on your own. " wrote: Jim Thanks for the information, but I do not have the luxury of making changes to the base format..... will be used by State & Feds. So I can't repeat names. I am designing only the totalling function for column 7 [Totals]. Here is real data information to give you a better idea. Name Type Number Value Units Totals ABROMS, G. NM16 3 EMPLOYER RESPONSE 5 15 x AXEL, R. CT14 74 RETURN TO WORK 5 370 x ML06 5 MASS LAYOFF ISSUE 3 15 NM02 1 ABILITY 9 9 NM06 5 AVAILABILITY 8 40 NM16 1 EMPLOYER RESPONSE 5 5 NM22 1 FIRED 43.6 43.6 NM42 2 REMUNERATION 12 24 NM58 3 SUFFICIENT WORK WAGE 5 15 ARMSTRONG, R. NM02 2 ABILITY 9 18 X NM06 4 AVAILABILITY 8 32 NM10 4 BASE PERIOD WAGES 12 48 NM16 14 EMPLOYER RESPONSE 5 70 NM22 108 FIRED 43.6 4708.8 NM40 59 QUIT 40 2360 NM42 2 REMUNERATION 12 24 NM46 1 SUITABLE WORK 15 15 NM56 18 VALID CLAIM 13 234 BROWN, B NM02 4 ABILITY 9 36 X NM06 9 AVAILABILITY 8 72 NM42 1 REMUNERATION 12 12 NM56 5 VALID CLAIM 13 65 NX04 1 INELIGIBILITY REVIEW 15 15 As you can see the number and type of cases varies, so I need to devise a programming or formula which totals the units column values for each staff member. Thanks Though. Pete Jim Thomlinson wrote: REally you need to copy the staff member names down so your table looks more like this Staff 1 Staff 1 data number Total Staff 1 data number Staff 2 Staff 2 data number Total Staff 2 data number Staff 2 data number Staff 2 data number Staff 3 Staff 3 data number Total Staff 3 data number Staff 3 data number After that getting the subtotals is not that terribley difficult. Yu could use subtotaling, pivot tables or even array formulas. Not sure which would be your favorite so I will let you decide that. To poplulate the first column do this Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now select all of row a which contains the staff members. A4 - A1000. Hit F5. Click on the Special Cells Button. Check off Blank cells. All of the blank cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first column should now be filled in. Copy and paste special values so that your formulas become hard coded values and you should be off to the races... HTH " wrote: Morning, I have a column of data (numbers) which extend down between rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
Are you wanting to sum the number, the value the units or the units times the
value? " wrote: Jim Thanks for the information, but I do not have the luxury of making changes to the base format..... will be used by State & Feds. So I can't repeat names. I am designing only the totalling function for column 7 [Totals]. Here is real data information to give you a better idea. Name Type Number Value Units Totals ABROMS, G. NM16 3 EMPLOYER RESPONSE 5 15 x AXEL, R. CT14 74 RETURN TO WORK 5 370 x ML06 5 MASS LAYOFF ISSUE 3 15 NM02 1 ABILITY 9 9 NM06 5 AVAILABILITY 8 40 NM16 1 EMPLOYER RESPONSE 5 5 NM22 1 FIRED 43.6 43.6 NM42 2 REMUNERATION 12 24 NM58 3 SUFFICIENT WORK WAGE 5 15 ARMSTRONG, R. NM02 2 ABILITY 9 18 X NM06 4 AVAILABILITY 8 32 NM10 4 BASE PERIOD WAGES 12 48 NM16 14 EMPLOYER RESPONSE 5 70 NM22 108 FIRED 43.6 4708.8 NM40 59 QUIT 40 2360 NM42 2 REMUNERATION 12 24 NM46 1 SUITABLE WORK 15 15 NM56 18 VALID CLAIM 13 234 BROWN, B NM02 4 ABILITY 9 36 X NM06 9 AVAILABILITY 8 72 NM42 1 REMUNERATION 12 12 NM56 5 VALID CLAIM 13 65 NX04 1 INELIGIBILITY REVIEW 15 15 As you can see the number and type of cases varies, so I need to devise a programming or formula which totals the units column values for each staff member. Thanks Though. Pete Jim Thomlinson wrote: REally you need to copy the staff member names down so your table looks more like this Staff 1 Staff 1 data number Total Staff 1 data number Staff 2 Staff 2 data number Total Staff 2 data number Staff 2 data number Staff 2 data number Staff 3 Staff 3 data number Total Staff 3 data number Staff 3 data number After that getting the subtotals is not that terribley difficult. Yu could use subtotaling, pivot tables or even array formulas. Not sure which would be your favorite so I will let you decide that. To poplulate the first column do this Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now select all of row a which contains the staff members. A4 - A1000. Hit F5. Click on the Special Cells Button. Check off Blank cells. All of the blank cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first column should now be filled in. Copy and paste special values so that your formulas become hard coded values and you should be off to the races... HTH " wrote: Morning, I have a column of data (numbers) which extend down between rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Intermitten Data in one Column
Here is some code. It assumes you want to sum up the 6th column. You can
change this by modifying the const at the top of the code. It also assumes that your first name is in Cell A2. You can modify that also... change Set rngName = wks.Range("A2") to whatever cell makes you the happiest.. Option Explicit Const m_cSumColumn As Integer = 5 Public Sub AddSubtotals() Dim wks As Worksheet Dim rngName As Range Dim rngToSum As Range Set wks = ActiveSheet Set rngName = wks.Range("A2") Set rngToSum = Range(rngName, rngName.End(xlDown)).Offset(0, m_cSumColumn) Do While rngName.Row < 65536 rngName.Offset(1, 6).Value = Application.Sum(rngToSum) Set rngName = rngName.End(xlDown) Set rngToSum = Range(rngName, rngName.End(xlDown)).Offset(0, m_cSumColumn) Loop End Sub HTH " wrote: Jim Thanks for the information, but I do not have the luxury of making changes to the base format..... will be used by State & Feds. So I can't repeat names. I am designing only the totalling function for column 7 [Totals]. Here is real data information to give you a better idea. Name Type Number Value Units Totals ABROMS, G. NM16 3 EMPLOYER RESPONSE 5 15 x AXEL, R. CT14 74 RETURN TO WORK 5 370 x ML06 5 MASS LAYOFF ISSUE 3 15 NM02 1 ABILITY 9 9 NM06 5 AVAILABILITY 8 40 NM16 1 EMPLOYER RESPONSE 5 5 NM22 1 FIRED 43.6 43.6 NM42 2 REMUNERATION 12 24 NM58 3 SUFFICIENT WORK WAGE 5 15 ARMSTRONG, R. NM02 2 ABILITY 9 18 X NM06 4 AVAILABILITY 8 32 NM10 4 BASE PERIOD WAGES 12 48 NM16 14 EMPLOYER RESPONSE 5 70 NM22 108 FIRED 43.6 4708.8 NM40 59 QUIT 40 2360 NM42 2 REMUNERATION 12 24 NM46 1 SUITABLE WORK 15 15 NM56 18 VALID CLAIM 13 234 BROWN, B NM02 4 ABILITY 9 36 X NM06 9 AVAILABILITY 8 72 NM42 1 REMUNERATION 12 12 NM56 5 VALID CLAIM 13 65 NX04 1 INELIGIBILITY REVIEW 15 15 As you can see the number and type of cases varies, so I need to devise a programming or formula which totals the units column values for each staff member. Thanks Though. Pete Jim Thomlinson wrote: REally you need to copy the staff member names down so your table looks more like this Staff 1 Staff 1 data number Total Staff 1 data number Staff 2 Staff 2 data number Total Staff 2 data number Staff 2 data number Staff 2 data number Staff 3 Staff 3 data number Total Staff 3 data number Staff 3 data number After that getting the subtotals is not that terribley difficult. Yu could use subtotaling, pivot tables or even array formulas. Not sure which would be your favorite so I will let you decide that. To poplulate the first column do this Staff 1 is in cell A4. In cell A5 put the formula =A4. Copy cell A5. Now select all of row a which contains the staff members. A4 - A1000. Hit F5. Click on the Special Cells Button. Check off Blank cells. All of the blank cells will now be highlighted. Now paste the formula. (Ctrl + v). Your first column should now be filled in. Copy and paste special values so that your formulas become hard coded values and you should be off to the races... HTH " wrote: Morning, I have a column of data (numbers) which extend down between rows 4 to 1000, the length varies month to month. The collected data in sepearated by spaces per staff members. I need to total the activity for that staff member. See below. Staff 1 data number Total data number Staff 2 data number Total data number data number data number Staff 3 data number Total data number data number Ect. As you can see the third column has the values I need to total for each staff member to place in the first available row in column 4. But since the number of values are difference from staff member to staff member I can not figure out how to sum the column? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Plotting multiple Y column data versus single X column in Excel 20 | Charts and Charting in Excel | |||
How do I sort data in column B to align with data in column A? | New Users to Excel | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
counting data in one column and match with data in another column | Excel Discussion (Misc queries) | |||
Matching one column against another column of data to show the same amount of data. | Excel Worksheet Functions |