Can anyone help please?

I have a table of staff members and tasks set out as follows (the real
one is a lot bigger)

Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 1 2 3 1 7
Staff member 1 2 4 1 5 12
Staff member 3 1 3 5 4 13
Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10
Staff member 5 1 3 3 5 12
Staff member 6 4 4 1 1 10
Staff member 6 3 2 3 2 10



I would like this table to end up with just one entry for each person
and their tasks added up

So that for instance for staff member 1,



Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 3 6 4 6 19

and so on.


Can anyone help with a macro thanks?

Janev

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To iLastRow
If Cells(i, "A").Value = Cells(i + 1, "A").Value Then
j = 1
Do While Cells(i + j, "A").Value = Cells(i, "A").Value
For k = 2 To 6
Cells(i, k).Value = Cells(i, k).Value + _
Cells(i + j, k).Value
Next k
j = j + 1
Loop
If rng Is Nothing Then
Set rng = Rows(i + j - 1)
Else
Set rng = Union(rng, Rows(i + j - 1))
End If
i = i + j - 1
End If
Next i

If Not rng Is Nothing Then rng.Delete
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Janev" wrote in message
...
Can anyone help please?

I have a table of staff members and tasks set out as follows (the real
one is a lot bigger)

Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 1 2 3 1 7
Staff member 1 2 4 1 5 12
Staff member 3 1 3 5 4 13
Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10
Staff member 5 1 3 3 5 12
Staff member 6 4 4 1 1 10
Staff member 6 3 2 3 2 10



I would like this table to end up with just one entry for each person
and their tasks added up

So that for instance for staff member 1,



Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 3 6 4 6 19

and so on.


Can anyone help with a macro thanks?

Janev

Bob,

Thought it was a super sub, notwithstanding Janev's preference for the
pivot-table approach <bg. Tested the sub with the data as posted. The sub
ended up with the desired results, but it apparently distinguished the case
for "Staff member 4", viz there were 2 lines:

Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10

Strangely, the pivot table doesn't have this case sensitivity issue, it gives:
Staff member 4 4 3 6 6 19

Is there a way to have your sub ignore the case and produce the same result
as the pivot table? Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max

In case Bob hasn't noticed your request, you can achieve what you want
by forcing the test to be Uppercase on both sides.
in two places within Bob's code.

If UCase(Cells(i, "A").Value) = UCase(Cells(i + 1, "A").Value) Then

and
Do While UCase(Cells(i + j, "A").Value) = UCase(Cells(i, "A").Value)

Bob may well have a better solution , but this will achieve what you
ask.


--
Regards

Roger Govier


"Max" wrote in message
...
Bob,

Thought it was a super sub, notwithstanding Janev's preference for the
pivot-table approach <bg. Tested the sub with the data as posted. The
sub
ended up with the desired results, but it apparently distinguished the
case
for "Staff member 4", viz there were 2 lines:

Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10

Strangely, the pivot table doesn't have this case sensitivity issue,
it gives:
Staff member 4 4 3 6 6 19

Is there a way to have your sub ignore the case and produce the same
result
as the pivot table? Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Yes, it does. Thanks for the tweaks, Roger!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote:
Hi Max

In case Bob hasn't noticed your request, you can achieve what you want
by forcing the test to be Uppercase on both sides.
in two places within Bob's code.

If UCase(Cells(i, "A").Value) = UCase(Cells(i + 1, "A").Value) Then

and
Do While UCase(Cells(i + j, "A").Value) = UCase(Cells(i, "A").Value)

Bob may well have a better solution , but this will achieve what you
ask.


--
Regards

Roger Govier

Hi Max

You're welcome.
But I would have gone with the PT solution myself, I love PT's!!!
Far too lazy to write the code as Bob does, but it's easy to tweak once
somebody else has done all the hard work.

--
Regards

Roger Govier


"Max" wrote in message
...
Yes, it does. Thanks for the tweaks, Roger!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Roger Govier" wrote:
Hi Max

In case Bob hasn't noticed your request, you can achieve what you
want
by forcing the test to be Uppercase on both sides.
in two places within Bob's code.

If UCase(Cells(i, "A").Value) = UCase(Cells(i + 1, "A").Value) Then

and
Do While UCase(Cells(i + j, "A").Value) = UCase(Cells(i, "A").Value)

Bob may well have a better solution , but this will achieve what you
ask.


--
Regards

Roger Govier

Hi Max,

I did notice that in my testing and decided it was a typo, so I changed it
in my tests. Shouldn't have I suppose, should have just tested it like our
Welsh friend suggested.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
Bob,

Thought it was a super sub, notwithstanding Janev's preference for the
pivot-table approach <bg. Tested the sub with the data as posted. The sub
ended up with the desired results, but it apparently distinguished the
case
for "Staff member 4", viz there were 2 lines:

Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10

Strangely, the pivot table doesn't have this case sensitivity issue, it
gives:
Staff member 4 4 3 6 6 19

Is there a way to have your sub ignore the case and produce the same
result
as the pivot table? Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks for the response, Bob !
Roger's suggested tweak rounded off your sub nicely.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote in message
...
Hi Max,

I did notice that in my testing and decided it was a typo, so I changed it
in my tests. Shouldn't have I suppose, should have just tested it like our
Welsh friend suggested.

--
HTH

Bob Phillips

Try a pivot table (PT) .. it's great for this kind of task .. 10 seconds only
<g

Steps below in xl2003 (but should be similar for earlier ver):
First, enter a label for col A, eg: Staff
Select any cell within the table, click Data Pivot table & PivotChart
Report
Click Next Next. In step 3, click Layout, drag n drop Staff in the ROW
area. Drag n drop Task 1 in DATA area. Repeat the drag n drop for Task
2,3,4,...Total (Drop each below the previous). Click OK Finish. The PT will
be created in a new sheet to the left. Go to the PT sheet, then just drag the
col header "Data" and drop it over "Total", and the resulting table will be
exactly what you want (do-able within 10-15 secs flat <g).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Janev" wrote:
Can anyone help please?

I have a table of staff members and tasks set out as follows (the real
one is a lot bigger)

Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 1 2 3 1 7
Staff member 1 2 4 1 5 12
Staff member 3 1 3 5 4 13
Staff member 4 1 2 4 2 9
staff member 4 3 1 2 4 10
Staff member 5 1 3 3 5 12
Staff member 6 4 4 1 1 10
Staff member 6 3 2 3 2 10



I would like this table to end up with just one entry for each person
and their tasks added up

So that for instance for staff member 1,



Task 1 Task 2 Task 3 Task 4 Total
Staff member 1 3 6 4 6 19

and so on.


Can anyone help with a macro thanks?

Janev

On Mon, 4 Sep 2006 02:54:01 -0700, Max wrote:


Thanks Max, that is briliiant, would have saved me hours of work today
trying to get dodgy macros to work.

Jan.
Try a pivot table (PT) .. it's great for this kind of task .. 10 seconds only
<g

Steps below in xl2003 (but should be similar for earlier ver):
First, enter a label for col A, eg: Staff
Select any cell within the table, click Data Pivot table & PivotChart
Report
Click Next Next. In step 3, click Layout, drag n drop Staff in the ROW
area. Drag n drop Task 1 in DATA area. Repeat the drag n drop for Task
2,3,4,...Total (Drop each below the previous). Click OK Finish. The PT will
be created in a new sheet to the left. Go to the PT sheet, then just drag the
col header "Data" and drop it over "Total", and the resulting table will be
exactly what you want (do-able within 10-15 secs flat <g).

How dare you call my macros dodgy <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Janev" wrote in message
...
On Mon, 4 Sep 2006 02:54:01 -0700, Max wrote:


Thanks Max, that is briliiant, would have saved me hours of work today
trying to get dodgy macros to work.

Jan.
Try a pivot table (PT) .. it's great for this kind of task .. 10 seconds
only
<g

Steps below in xl2003 (but should be similar for earlier ver):
First, enter a label for col A, eg: Staff
Select any cell within the table, click Data Pivot table & PivotChart
Report
Click Next Next. In step 3, click Layout, drag n drop Staff in the ROW
area. Drag n drop Task 1 in DATA area. Repeat the drag n drop for Task
2,3,4,...Total (Drop each below the previous). Click OK Finish. The PT
will
be created in a new sheet to the left. Go to the PT sheet, then just drag
the
col header "Data" and drop it over "Total", and the resulting table will
be
exactly what you want (do-able within 10-15 secs flat <g).

On Mon, 4 Sep 2006 11:56:49 +0100, "Bob Phillips"
wrote:

How dare you call my macros dodgy <bg

I didn't mean your macro Bob, I was referring to another macro I was
given by a tech. support person which didn't seem to work very well
- I can home tonight and thought I'd consult the experts.

I appreciate your help.

Thank you,

Jan.

..

Only kidding ... I did add a <bg (big grin)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Janev" wrote in message
...
On Mon, 4 Sep 2006 11:56:49 +0100, "Bob Phillips"
wrote:

How dare you call my macros dodgy <bg

I didn't mean your macro Bob, I was referring to another macro I was
given by a tech. support person which didn't seem to work very well
- I can home tonight and thought I'd consult the experts.

I appreciate your help.

Thank you,

Jan.

.

Hullo Everyone out there,

I went to work and tried the PT solution and it worked, well, EXCEPT I
forgot to add a column in my dummy table.
What I'm working with is a column with a payroll number as well as the
name and the task columns.

Can anyone help with this permutation please?

I have a table of staff members and tasks set out as follows (the real
one is a lot bigger)


Payroll
No. Name Task 1 Task 2 Task 3 Task 4 Total
423 Staff member 1 1 2 3 1 7
555 Staff member 2 1 2 4 1 5
108 Staff member 3 3 1 3 5 4
321 Staff member 4 1 2 4 2 9
321 staff member 4 3 1 2 4 10
123 Staff member 5 1 3 3 5 12
432 Staff member 6 4 4 1 1 10
432 Staff member 6 3 2 3 2 10


Thanks,

Janev

Hi Janev

No problem, just extend the data range in the Pivot Table. If the table
is likely to grow in length, it would probably be best to set up a
defined dynamic range for the source data.
InsertNameDefineName Mydata Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),7)

When setting up the PT, instead of pointing to the range, or accepting
what Excel thinks is the range, enter =Mydata.
In addition to Max's other instruction, in the Layout section drag
Payroll Number to the Row Area, and place it above Staff Member.
When you are viewing the final PT, double click on the Field name
Payroll Number and set Subtotals to None.

Post back if you have any more difficulties.
--
Regards

Roger Govier


"Janev" wrote in message
...
Hullo Everyone out there,

I went to work and tried the PT solution and it worked, well, EXCEPT I
forgot to add a column in my dummy table.
What I'm working with is a column with a payroll number as well as the
name and the task columns.

Can anyone help with this permutation please?

I have a table of staff members and tasks set out as follows (the real
one is a lot bigger)


Payroll
No. Name Task 1 Task 2 Task 3 Task 4 Total
423 Staff member 1 1 2 3 1 7
555 Staff member 2 1 2 4 1 5
108 Staff member 3 3 1 3 5 4
321 Staff member 4 1 2 4 2 9
321 staff member 4 3 1 2 4 10
123 Staff member 5 1 3 3 5 12
432 Staff member 6 4 4 1 1 10
432 Staff member 6 3 2 3 2 10


Thanks,

Janev

Thanks Roger, Worked like a dream!

Saved heaps of time - good work!!!

Janeve


On Tue, 5 Sep 2006 19:33:45 +0100, "Roger Govier"
wrote:

Hi Janev

No problem, just extend the data range in the Pivot Table. If the table
is likely to grow in length, it would probably be best to set up a
defined dynamic range for the source data.
InsertNameDefineName Mydata Refers to
=OFFSET($A$1,0,0,COUNTA($A:$A),7)

When setting up the PT, instead of pointing to the range, or accepting
what Excel thinks is the range, enter =Mydata.
In addition to Max's other instruction, in the Layout section drag
Payroll Number to the Row Area, and place it above Staff Member.
When you are viewing the final PT, double click on the Field name
Payroll Number and set Subtotals to None.

Post back if you have any more difficulties.