Adding rows in Excel worksheets and 'deduping'

[Janev]

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 Phillips]

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)

 

[Guest]

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>).

 

[Janev]

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

Jan.

 

[Bob Phillips]

How dare you call my macros dodgy <bg>

--
HTH

Bob Phillips

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

 

[Janev]

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.

..

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Janev]

Hah, I now know about <bg> as well!

Once again,
thanks,
Jan

 

[Guest]

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.

---

 

[Guest]

Janev,

Glad one of the suggestions was to your taste
(it just happens to be mine this round)

Notwithstanding the preference for the pivot table here,
think it's great to also study and keep Bob's sub handy ..

---

 

[Roger Govier]

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.

 

[Guest]

Yes, it does. Thanks for the tweaks, Roger!

 

[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.

 

[Bob Phillips]

If you recall Roger, I am a PT sceptic. Along with Biff I have a real
problem with them (I think they are a rubbish implementation of a good
idea).

--
HTH

Bob Phillips

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

 

[Bob Phillips]

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)

 

[Roger Govier]

Hi Bob

I knew you weren't keen, but I didn't realise you hated them so
vehemently<bg>.

I find them very useful, and so fast especially if you want to take
differing views of the same data.
I know you can achieve the same, or similar, results through the use of
other formulae and or VBA approaches, but in most cases that requires a
much better skill set (especially when it come to writing code), which
you most clearly do possess.
Others, myself included, are not so good and PT's provide a very quick
solution.

I just wondered why you think their implementation is so bad.

--
Regards

Roger Govier

If you recall Roger, I am a PT sceptic. Along with Biff I have a real
problem with them (I think they are a rubbish implementation of a good
idea).

--
HTH

Bob Phillips

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

 

[Bob Phillips]

Hi Roger,

I use them, but because of my distinct lack of enthusiasm, I probably
under-use. I am in a current phase Of trying to use them more (even reading
Debra's book).

As for poor implementation, I could go on. I think the object model is
rubbish, it doesn't automatically refresh, etc. etc. As an example, I tried
to extract the data from a PT in VBA the other day, and ended up going back
to the source data. I am sure it can be done, it was just too much effort
for me.

--
HTH

Bob Phillips

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

 

[Janev]

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

 

[Roger Govier]

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.
Insert>Name>Define>Name 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.

 

[Max]

Thanks for the response, Bob !
Roger's suggested tweak rounded off your sub nicely.