Results 1 to 7 of 7
  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 Comments (2007)

    Loungers - heres another one - If you see the attached sample, I have a input tab and I'm want to be able to summarise the comments (removing any duplicate comments) into the corresponding merged cells in the summary tab.

    I image that this will require some VBA code, but thats a bit beyond me at this stage.

    Any thoughts?
    Attached Files Attached Files

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

    Re: Summarising Comments (2007)

    Set the two output cells to Wrap Text and set their vertical alignment to Top.

    You can use the following code:

    Sub FillSummary()
    ' Production Summary
    FillOne "D", "D4"
    ' Maintenance
    FillOne "E", "D17"
    End Sub

    Sub FillOne(SourceCol As String, TargetCell As String)
    Dim wshIn As Worksheet
    Dim wshOut As Worksheet
    Dim r As Long
    Dim m As Long
    Dim col As Collection
    Dim itm As Variant
    Dim strText As String

    Set wshIn = Worksheets("Input")
    Set wshOut = Worksheets("Summary")

    On Error Resume Next

    Set col = New Collection
    ' Collect unique entries
    m = wshIn.Range(SourceCol & Rows.Count).End(xlUp).Row
    For r = 4 To m
    If Not wshIn.Range(SourceCol & r) = "" Then
    col.Add wshIn.Range(SourceCol & r), CStr(wshIn.Range(SourceCol & r))
    End If
    Next r
    ' Create string
    strText = ""
    For Each itm In col
    strText = strText & vbLf & itm
    Next itm
    ' Get rid of first line feed
    If Not strText = "" Then
    strText = Mid(strText, 2)
    End If
    ' Fill summary cell
    wshOut.Range(TargetCell) = strText
    End Sub

    Note: the word Maintenance was misspelled twice.

    See attached version.
    Attached Files Attached Files

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

    Re: Summarising Comments (2007)

    Hans - Thanks again

    In the working spreadsheet the name of the tabs are different - I assume by looking at the code it is just a matter of changing this
    Set wshIn = Worksheets("new Input")
    Set wshOut = Worksheets("new Summary")
    to match the different tab names or is there any thing else that needs adjusting?

    Thanks for drawing my attention to the spelling error

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

    Re: Summarising Comments (2007)

    Those lines are the only ones that you have to change.

  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 Comments (2007)

    Thanks Hans
    Just really for my knowledge. What dose the "D" and "E" mean in FillOne "D", "D4" FillOne "E", "D17" - I understand the "D4" and the "D17" relating to the cells,
    and I will need to change the columns numbers that the information is collected from - I assume that this happens here - For r = 4 To m. Where the next column is say 10 how would that change the code?

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

    Re: Summarising Comments (2007)

    The syntax of FillOne is

    FillOne SourceCol, TargetCell

    where SourceCol is the name of the column on the source (input) sheet that contains the items that you want to summarize, and TargetCell is the name of the merged cell in which you want the summary.

    In your sample workbook, the Production Summary data are in column D on the source sheet, and you want to put them in cell D4 on the summary sheet, hence

    FillOne "D", "D4"

    The Maintenance data are in column E on the source sheet, and you want to put them in cell D17 on the summary sheet, hence

    FillOne "E", "D17"

    If you had data in column P on the source sheet, and if you wanted to summarize them in cell G37 on the summary sheet, you'd use

    FillOne "P", "G37"

    On your source sheet, the data begin in row 4, below the column header in row 3. The code calculates the last used row - this is the variable m. So

    For r = 4 To m
    ...
    Next r

    means that the code loops through rows 4 to m. If the data on your real source sheet begin in row 11 instead of in row 4, you'd use

    For r = 11 To m

    There's no need to change the m - it's calculated automatically.

  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 Comments (2007)

    Hans - where do you get this from? Amazing!!

    Your assistance is very much appreciated be me and I'm sure by the rest of the loungers.

    I'd have to say that I've learnt more from Woody's (and of course you) than any other source - however it continues to show how much I don't know and how much there is to learn.

Posting Permissions

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