 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006Adding 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 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  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Can anyone help please? > [quoted text clipped - 24 lines] > > 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. SignatureMax 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. SignatureRegards Roger Govier > Bob, > [quoted text clipped - 15 lines] > result > as the pivot table? Thanks. Yes, it does. Thanks for the tweaks, Roger! SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi Max > [quoted text clipped - 9 lines] > Bob may well have a better solution , but this will achieve what you > ask. 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. SignatureRegards Roger Govier > Yes, it does. Thanks for the tweaks, Roger! >> Hi Max [quoted text clipped - 11 lines] >> Bob may well have a better solution , but this will achieve what you >> ask. 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). SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hi Max > [quoted text clipped - 18 lines] > >> Bob may well have a better solution , but this will achieve what you > >> ask. 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. SignatureRegards 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 [quoted text clipped - 26 lines] >> >> you >> >> ask. 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. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hi Bob > [quoted text clipped - 42 lines] > >> >> you > >> >> ask. 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. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Bob, > [quoted text clipped - 11 lines] > Is there a way to have your sub ignore the case and produce the same result > as the pivot table? Thanks. Thanks for the response, Bob ! Roger's suggested tweak rounded off your sub nicely. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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. 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>). SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Can anyone help please? > [quoted text clipped - 24 lines] > > Janev 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> [quoted text clipped - 9 lines] >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> SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Thanks Max, that is briliiant, would have saved me hours of work today > trying to get dodgy macros to work. [quoted text clipped - 13 lines] > >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> 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) SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > >How dare you call my macros dodgy <bg> > [quoted text clipped - 9 lines] > > . Hah, I now know about <bg> as well! Once again, thanks, Jan >Only kidding ... I did add a <bg> (big grin) 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 .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- 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. 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. SignatureRegards Roger Govier > Hullo Everyone out there, > [quoted text clipped - 22 lines] > > Janev Thanks Roger, Worked like a dream! Saved heaps of time - good work!!! Janeve >Hi Janev > [quoted text clipped - 12 lines] > >Post back if you have any more difficulties.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.