|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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" <newcole[ at ]comcen.com.au> wrote in message
news:agpnf2pua06fkncb479akfb0rd7te918lb[ at ]4ax.com...
[Quoted Text] > 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
|
|
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:
[Quoted Text] > 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 <demechanik[ at ]yahoo.com> wrote:
Thanks Max, that is briliiant, would have saved me hours of work today
trying to get dodgy macros to work.
Jan.
[Quoted Text] >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" <newcole[ at ]comcen.com.au> wrote in message
news:dbvnf2tsavmhpaa5hs0slv2aamso1ajv2s[ at ]4ax.com...
[Quoted Text] > On Mon, 4 Sep 2006 02:54:01 -0700, Max <demechanik[ at ]yahoo.com> 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"
<bob.NGs[ at ]somewhere.com> wrote:
[Quoted Text] >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" <newcole[ at ]comcen.com.au> wrote in message
news:eb2of2dmdnn83tf55o5cigsbdii91vi689[ at ]4ax.com...
[Quoted Text] > On Mon, 4 Sep 2006 11:56:49 +0100, "Bob Phillips"
> <bob.NGs[ at ]somewhere.com> 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.
>
> .
>
>
|
|
Hah, I now know about <bg> as well!
Once again,
thanks,
Jan
On Mon, 4 Sep 2006 13:02:07 +0100, "Bob Phillips"
<bob.NGs[ at ]somewhere.com> wrote:
[Quoted Text] >Only kidding ... I did add a <bg> (big grin)
|
|
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
---
|
|
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
---
|
|
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" <demechanik[ at ]yahoo.com> wrote in message
news:3DC3DA6B-8AE5-4D93-9CB6-34AE9F133C62[ at ]microsoft.com...
[Quoted Text] > 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:
[Quoted Text] > 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" <demechanik[ at ]yahoo.com> wrote in message
news:D3B1DB57-E1C2-4FDD-BAAF-B65B69A81D96[ at ]microsoft.com...
[Quoted Text] > 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
|
|
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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message
news:e5Gr4qM0GHA.4264[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > 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" <demechanik[ at ]yahoo.com> wrote in message > news:D3B1DB57-E1C2-4FDD-BAAF-B65B69A81D96[ at ]microsoft.com... > > 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" <demechanik[ at ]yahoo.com> wrote in message
news:3DC3DA6B-8AE5-4D93-9CB6-34AE9F133C62[ at ]microsoft.com...
[Quoted Text] > 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 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" <bob.NGs[ at ]somewhere.com> wrote in message
news:eX7qptM0GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message > news:e5Gr4qM0GHA.4264[ at ]TK2MSFTNGP05.phx.gbl... >> 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" <demechanik[ at ]yahoo.com> wrote in message >> news:D3B1DB57-E1C2-4FDD-BAAF-B65B69A81D96[ at ]microsoft.com... >> > 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 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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message
news:OlrDJ9M0GHA.4932[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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" <bob.NGs[ at ]somewhere.com> wrote in message > news:eX7qptM0GHA.1256[ at ]TK2MSFTNGP04.phx.gbl... > > 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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message > > news:e5Gr4qM0GHA.4264[ at ]TK2MSFTNGP05.phx.gbl... > >> 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" <demechanik[ at ]yahoo.com> wrote in message > >> news:D3B1DB57-E1C2-4FDD-BAAF-B65B69A81D96[ at ]microsoft.com... > >> > 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 > >> > >> > > > > > >
|
|
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.
[Quoted Text] >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.
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.
--
Regards
Roger Govier
"Janev" <newcole[ at ]comcen.com.au> wrote in message
news:b2iqf2l6j7nmli09iqo6et1kk3d9ne4e4b[ at ]4ax.com...
[Quoted Text] > 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 for the response, Bob !
Roger's suggested tweak rounded off your sub nicely.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
news:OOGTOuM0GHA.3656[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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
|
|
Thanks Roger, Worked like a dream!
Saved heaps of time - good work!!!
Janeve
On Tue, 5 Sep 2006 19:33:45 +0100, "Roger Govier"
<roger[ at ]technologyNOSPAM4u.co.uk> wrote:
[Quoted Text] >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.
|
|
|