Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping'

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding rows in Excel worksheets and 'deduping'

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping'

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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping'

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.

..


  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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.

.




  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping'

Hah, I now know about <bg as well!

Once again,
thanks,
Jan


On Mon, 4 Sep 2006 13:02:07 +0100, "Bob Phillips"
wrote:

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding rows in Excel worksheets and 'deduping'

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
---
  #10   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding rows in Excel worksheets and 'deduping'

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Adding rows in Excel worksheets and 'deduping'

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



  #12   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding rows in Excel worksheets and 'deduping'

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

  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Adding rows in Excel worksheets and 'deduping'

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



  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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)

"Roger Govier" wrote in message
...
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





  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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





  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Adding rows in Excel worksheets and 'deduping'

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


"Bob Phillips" wrote in message
...
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)

"Roger Govier" wrote in message
...
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







  #17   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 10,593
Default Adding rows in Excel worksheets and 'deduping'

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)

"Roger Govier" wrote in message
...
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


"Bob Phillips" wrote in message
...
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)

"Roger Govier" wrote in message
...
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








  #18   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping' - an extra twist

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
  #19   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,886
Default Adding rows in Excel worksheets and 'deduping' - an extra twist

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



  #20   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding rows in Excel worksheets and 'deduping'

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





  #21   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default Adding rows in Excel worksheets and 'deduping' - an extra twist

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 07:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"