Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|