Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    summarising cell value (2007)

    Loungers - need your assistance again.

    I have a list of actions entered in a column - I'm trying to summarise these action but don't want to include N/A's or duplicate actions.

    The attached spreadsheet example should help to show what I'm trying to achieve - I hope that all makes sense(?)

    Any thought/solutions would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: summarising cell value (2007)

    Have you though of using a pivot table, see attached. If that is not suitable then I think it will require VBA and I will leave that to somebody else <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: summarising cell value (2007)

    Sorry, can't help - can't open the file.

    That's the problem with Office 2007 - you can't share files with people who don't have that version.

    Well, you can of course, by choosing a different file format; but by default you end up with non-sharable files.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Sorry about that - try the .xls version
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Tony, Thanks for your help - the problem with the pivot table is that in the working file, the comment summary cells are merged ans I don't think that you can use merged cells with a pivot table.

    I suspected that VBA maybe necessary also - so I hope there is a VBA expert out there - based on previous experience with the Lounge I'll sure there will be

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summarising cell value (2007)

    Try this:

    Sub Summarize()
    Dim col As New Collection
    Dim r As Long
    Dim n As Long
    Dim strComments As String
    ' Last row in column G
    n = Cells(Rows.Count, 7).End(xlUp).Row
    On Error Resume Next
    ' Loop through comments
    For r = 4 To n
    ' Skip N/A
    If Not Cells(r, 7) = "N/A" Then
    ' Add comment to collection
    col.Add Item:=Cells(r, 7), Key:=Cells(r, 7)
    End If
    Next r
    ' Concatenate comments
    For r = 1 To col.Count
    strComments = strComments & ", " & colŪ
    Next r
    ' Omit first ", "
    If Not strComments = "" Then
    strComments = Mid(strComments, 3)
    End If
    ' Enter concatenated comments in B16
    Cells(16, 2) = strComments
    ' Clean up
    Set col = Nothing
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Thanks Hans - works great.
    If I want to restrict the range in G to say G3 to G30 how would the code change?

    Also is there a way to get the macro to run automatically as the working spreadsheet will be used by others that may not know or be aware of the macro?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summarising cell value (2007)

    You can use the Worksheet_Change event of the sheet containing the comments. See the attached version.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As New Collection
    Dim r As Long
    Dim strComments As String
    ' Did any of the cells in G4:G30 change?
    If Intersect(Range("G4:G30"), Target) Is Nothing Then
    ' No, get out
    Exit Sub
    End If
    ' Turn off event handling
    Application.EnableEvents = False
    On Error Resume Next
    ' Loop through comments
    For r = 4 To 30
    ' Skip N/A
    If Not Cells(r, 7) = "N/A" Then
    ' Add comment to collection
    col.Add Item:=Cells(r, 7), Key:=Cells(r, 7)
    End If
    Next r
    ' Concatenate comments
    For r = 1 To col.Count
    strComments = strComments & ", " & colŪ
    Next r
    ' Omit first ", "
    If Not strComments = "" Then
    strComments = Mid(strComments, 3)
    End If
    ' Enter concatenated comments in B16
    Cells(16, 2) = strComments
    ' Clean up
    Set col = Nothing
    Application.EnableEvents = True
    End Sub
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Hans - you've done it again - Thanks very much!

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Hans - another question - if I want to exclude another word - say "test" in addition to "N/A" how would your code change?

    I've tried different variations but keep getting an error.

    Thanks for your help

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summarising cell value (2007)

    Change the part

    If Not Cells(r, 7) = "N/A" Then
    ' Add comment to collection
    col.Add Item:=Cells(r, 7), Key:=Cells(r, 7)
    End If

    to

    Select Case Cells(r, 7)
    Case "N/A", "test"
    ' Skip - do nothing
    Case Else
    ' Add comment to collection
    col.Add Item:=Cells(r, 7), Key:=Cells(r, 7)
    End Select

    You can add more items, separated by commas, to

    Case "N/A", "test"

    if desired.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Thanks for the quick reply and your solution

  13. #13
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Hans - sorry to be a pain.

    The attached example is a cut down version of the actual spreadsheet I need to apply your code.

    I've tried to modify to put comments in the control cells (D62 merged), but can't seem to get it to work.

    As I'm a bit of a novice with VBA I changed what seems to be obvious - like cell references etc, but not sure what else is required.

    I'm sure the solution is simple once you know how.

    Any ideas?
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: summarising cell value (2007)

    You're now using column I instead of column G. Column I is the 9th column, so you must change all occurrences of Cells(r, 7) to Cells(r, 9)

  15. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: summarising cell value (2007)

    Hans - Thanks

    I should have realized that - obvious when you point it out

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •