Note Summay with a twist

[austin350s10]

I am working on a large document where the user has the option of entering notes in many different cells on the worksheet. I would like a way pull out all the text they enter in a clean way and summarize it. When I say "clean" I mean locate only the cells they entered a note into and summarize it, in order, on a different sheet.

Each not section has a label associated with it so I was also wondering if the label could be plugged in and then the note. Then the next label and note...etc...

Example of Large Workbook

C10 = Label1 AC13 = Note Section1 (has text in it entered by user)
C24 = Label2 AC30 = Note Section2 (has no text in it)
C43 = Label3 AC45 = Note Section3 (has text in it entered by user)
C70 = Label4 AC78 = Note Section4 (has no text in it)
.........

Example of Summary Sheet

A1= "Lable1: "&AC13 +
CHAR(10)&"Label2: "&AC45
......

Is there any way to do something like this?

 

[austin350s10]

any ideas??

 

[Ruddles]

How do we determine which label is associated with which note?

What's your competency level with VBA? Do you want some advice on how to write the code or do you want someone to write it for you?

 

[austin350s10]

There is not real pattern to the note labels. The labels for the notes are just a cell reference or the labels could just be incorporated into the code. I would def need help writing this code though. Still really new to VBA.

 

[Ruddles]

Nope, I'm not getting it. You say, "Each note section has a label associated with it", but then, "There is no real pattern to the note labels".

Do you just want some code to look through columns C and AC and extract any data it finds, then copy it to two columns in a new worksheet?

I'm not really sure what A1= "Lable1: "&AC13 + CHAR(10)&"Label2: "&AC45 is meant to be doing.

 

[austin350s10]

The image below is a better example of what the user is entering notes into:

The image below is an example for the notes summarized with labels all into one cell A56:

Is something like this doable with a formula or VBA??

 

[Ruddles]

Personally I wouldn't even try with a formula, but then I tend to rush off to VBA whenever there's anything even remotely complicated. I find that laying out some VBA code neatly complete with explanatory comments is preferable to cramming lengthy combinations of nested Excel functions into a single cell. But that's just my opinion.

As regards your problem, perhaps you'd care to give this code a try?

Press Alt-F11 to open Microsoft Visual Basic, press Ctrl-R to view the Project Explorer, then double-click the name of your worksheet. Paste this code into the code window, make sure the CONSTs are all pointing to the correct places, then run it.

Option Explicit
 
Const DataLabel As String = "C"     ' column where the labels are located
Const DataNote As String = "AC"     ' column where the notes are located
Const DataRow As Integer = 35       ' first row containing the notes
Const SummaryCell As String = "A1"  ' where we write the summary
 
Public Sub WriteSummary()
 
  Dim lastRow As Integer
  Dim iPtr As Integer
 
  Application.ScreenUpdating = False
 
  lastRow = Cells(Rows.Count, DataNote).End(xlUp).Row
  Range(SummaryCell).ClearContents
 
  For iPtr = DataRow To lastRow
    If Not IsEmpty(Cells(iPtr, DataNote)) Then
      If IsEmpty(Range(SummaryCell)) Then
        Range(SummaryCell) = MergedValue(iPtr, DataLabel) & ": " & Cells(iPtr, DataNote)
      Else
        Range(SummaryCell) = Range(SummaryCell) & vbCrLf & MergedValue(iPtr, DataLabel) & ": " & Cells(iPtr, DataNote)
      End If
    End If
  Next iPtr
 
  Application.ScreenUpdating = True
 
End Sub
 
Private Function MergedValue(argRow, argColumn) As String
 
  Dim iScan As Integer
 
 
  For iScan = argRow To DataRow Step -1
    If Not IsEmpty(Cells(iScan, argColumn)) Then
      MergedValue = Cells(iScan, argColumn)
      Exit Function
    End If
  Next iScan
 
End Function

Let me know how it goes?

 

[austin350s10]

That seems like it would work but that would end up summarizing all the text in column AC 35 To last row. I have other text in the AC column that I do not want to be part of this summary. Is there a way to declare the cells to check as a constant and then run a script to loop through them checking to see if they contain any data?

 

[Ruddles]

You could merely set lastRow to point to the last row of your data but to make the code more consistent we can change it to set the value at the top. The numbers in red should point to the top and bottom cells which you want to summarise:-

Option Explicit
 
Const DataLabel As String = "C"     ' column where the labels are located
Const DataNote As String = "AC"     ' column where the notes are located
Const DataRow As Integer = [COLOR=red]186[/COLOR]      ' first row containing the notes
Const DataEnd As Integer = [COLOR=red]250[/COLOR]      ' last row containing the notes
Const SummaryCell As String = "A1"  ' where we write the summary
 
Public Sub WriteSummary()
 
  Dim iPtr As Integer
 
 
  Application.ScreenUpdating = False
 
  Range(SummaryCell).ClearContents
 
  For iPtr = DataRow To DataEnd
    If Not IsEmpty(Cells(iPtr, DataNote)) Then
      If IsEmpty(Range(SummaryCell)) Then
        Range(SummaryCell) = MergedValue(iPtr, DataLabel) & ": " & Cells(iPtr, DataNote)
      Else
        Range(SummaryCell) = Range(SummaryCell) & vbCrLf & MergedValue(iPtr, DataLabel) & ": " & Cells(iPtr, DataNote)
      End If
    End If
  Next iPtr
 
  Application.ScreenUpdating = True
 
End Sub
 
Private Function MergedValue(argRow, argColumn) As String
 
  Dim iScan As Integer
 
 
  For iScan = argRow To DataRow Step -1
    If Not IsEmpty(Cells(iScan, argColumn)) Then
      MergedValue = Cells(iScan, argColumn)
      Exit Function
    End If
  Next iScan
 
End Function

 

[austin350s10]

That is a little closer but still would collect everything in the range. Below is an example of the cells I would want to collect data from:

AC35 = Note Area (collect)
AC45 = Note Area (collect)
AC67 = Non-Note Area (do not collect)
AC90 = Note Area (collect)

There are many others that do not need to be collected in the full range. I know the script would be kinda long and might run a little slower but I would be fine with just telling the script which cells to check. There are only 23 note areas and 20 note labels in total. Could that work?