Creating Sentence Outputs with Example

[mkuczyns112]

Hello All,

Thanks in advance for the support. I am looking into creating a text output file. I have 3 inputs item, sentence, figure. The point is to create an output file from the positioning of the item in a sentence and inputing a figure where applicable after the sentence.

Imagine Column A as the item, Column B as which sentence the item goes into and Column C as the figure. The figure will follow the next x.

ITEM SENTENCE FIGURE
dog 1 ""
cat 1 ""
gate 1x a
horse 2 ""
moose 2 ""
cow 2 ""
cave 2x b

Note: the designator of x signifies the item to be at the end of the sentence. The figure (can be changed) is setup to write a code to imply a figure is needed at this point.

The structure of the sentence should be the following:

1. Remove dog, cat from gate.
ADD FIGURE
2. Remove horse, moose, cow from cave.
ADD FIGURE

Note: Always start the sentence off with "Remove" and the "x designator" should have the word "from" prior to it's use. The order of which the items appear is irrelevant.

Would like file saved as h:\test.txt

I understand that this can be complex. If so, a discussion on how to create the structure would be appreciated as well. I hope I covered all variables, if there are any questions let me know.

 

[bertie]

Hi, and welcome to the forum.

I assumed your sample data is on Sheet1.

To use the code:
Press Alt+F11 to open the vba editor window.
Double click on the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code.
Edit where highlighted if necessary.

Sub CreateSentence()
   Dim strTemp As String
   Dim strOutput As String
   Dim rng As Range
   
   Set rng = Sheets("Sheet1").Range("A2")
   
   Do Until rng = ""
      'build a new sentence
      If strTemp = "" Then strTemp = "Remove "
      
      'is it the end of a sentence?
      If UCase(Right(rng.Offset(, 1), 1)) = "X" Then
         'remove final comma
         strTemp = Mid(strTemp, 1, Len(strTemp) - 2)
         
         'build output string
         strTemp = strTemp & " from " & rng.Value & "."
         strOutput = strOutput & strTemp & vbCrLf & rng.Offset(, 2).Value & vbCrLf
         strTemp = ""
      Else
         'part of the same sentence
         strTemp = strTemp & rng.Value & ", "
      End If
      
      'next row
      Set rng = rng.Offset(1, 0)
   Loop
   
   OutputFile strOutput
   
   'tidy up
   Set rng = Nothing
End Sub


Private Sub OutputFile(ByVal myString As String)
   Dim fileName As String
   Dim fNum As Long


   fileName = "h:\test.txt"
   fNum = FreeFile()
   
   Open fileName For Output As #fNum
   Print #fNum, myString
   Close #fNum
End Sub

 

[mkuczyns112]

This is a great start Bertie. Much appreciated.

 

[mkuczyns112]

I have a few questions.

So the code provided is basically scanning for the "X". It will begin outputting "Remove" then adding what is in column A until it runs into an X. Once it sees the X it places "from" in front of it and places what is in column C after it. Essentially the Column B numbering sequence does not matter.

I don't see how to manipulate what was provided if columb B (sentence) sequence is changed. I guess my question is how do I make the numbering system relevant? What if I changed above from:
ITEM SENTENCE FIGURE
dog 1 ""
cat 1 ""
gate 1x a
horse 2 ""
moose 2 ""
cow 2 ""
cave 2x b

to

ITEM SENTENCE FIGURE
dog 2 ""
cat 2 ""
gate 1x a
horse 1 ""
moose 1 ""
cow 1 ""
cave 2x b

The output becomes Remove horse, moose, cow from gate... ect.

Also, to begin a new sentence, how do I add a numberical system. 1. Remove.... 2. Remove....

Thanks Again.

 

[bertie]

The simple part first, add a numerical system, i.e., 1.Remove...2.Remove...etc.

Based on my original code consider this snippet:

      'build a new sentence
      If strTemp = "" Then strTemp = "Remove "

The number to append to the from of "Remove" is in the adjacent cell to the right.
So we just concatenate that in:

      'build a new sentence
      If strTemp = "" Then strTemp = rng.Offset(, 1).Value & ". " & "Remove "

You described perfectly how the code works. This,however, means the number sequence is relevant. This way, the code loops through the data once.

For an out of sequence data set we would potentially need to loop through the data the same number of times as the number of data entries; very inefficient.
For example, take your second data set.
First we would find the sentence number 2.
Check to see #2 has already been processed.
Loop through the entire column to find all instances of #2.
---build the comma separated string
---build the from string

Rinse and repeat until data is exhausted.

Like I said, very inefficient, especially for large data sets.

 

[bertie]

Follow up thoughts.

Sort the data on column B, the column with the numbers.
This will place the numbers at the top, with the, "x's" grouped at the bottom.

Loop through the data as before to build the comma separated string.
When the next row is a different number
---use a Find() routine to get the row, e.g., 2x is on
--- build the FROM string

This way we would still only need to loop through the data once.

 

[mkuczyns112]

I read the first response and thought the same thing. Filtering would work if each item had a quantity of 1. If you add additional quanitites such as 2 cats and want to use them in sentence 1 and 2, the solution seems you would have to add another column to loop thru or create an item of cat, cat.

In terms of an inefficient code, is the issue space or time to run?

Appreciate your time in answering.

 

[mkuczyns112]

I got another interisting idea.

What about another code for quantity separation?

So my inputs are items and quantities.

cat 3
dog 3
gate 2

Do you think a code can be written to search column B (QTY) and create new line item in excel in column C of

cat
cat
cat
dog
dog
dog
gate
gate

Look forward to response!

 

[mkuczyns112]

In addition, is vba "efficient" or capable enough to recognize that if I have 2 of the same item in the same sentence to put a numerical representation of what that is in the sentence.

Example,

cat 1
cat 1
cat 2

Output:
1. Remove 2 cats
2. Remove cat

 

[bertie]

The inefficiency would be in the time to run, based on the sizw of the data set.

I am confused with your last two posts. I'm not clear what you are trying to do here. So let's go back to your post #4, non sequential list

ITEM SENTENCE FIGURE
dog 2 ""
cat 2 ""
gate 1x a
horse 1 ""
moose 1 ""
cow 1 ""
cave 2x b

The output becomes Remove horse, moose, cow from gate... ect.

And my second proposed solution, post #6

Loop through the data as before to build the comma separated string.
When the next row is a different number
---use a Find() routine to get the row, e.g., 2x is on
--- build the FROM string


Try the code below.
Press F8 to step through the code and place the cursor over the variables to see their contents.
NB The output file is now Opened as APPEND
Edit the output file path

Option Explicit


Sub CreateSentence02()
   Dim strFind As String
   Dim strOutput As String
   Dim rngOuter As Range      'loops through data
   Dim rngInner As Range      'loops through like numbers
   Dim rngFound As Range
   
   'sort sheet1 on column B
   Worksheets("Sheet1").Range("B1").Sort _
        Key1:=Worksheets("Sheet1").Columns("B"), _
        Header:=xlYes
   
   'loop through the data on column B
   Set rngOuter = Sheets("Sheet1").Range("B2")
   Do Until rngOuter = ""
      
      'set the inner loop
      Set rngInner = rngOuter
         
      'don't process X values
      If IsNumeric(rngInner.Value) Then
         
         'start the output string
         strOutput = rngOuter.Value & ". Replace "
         
         'build the comma separated string
         Do While rngInner.Value = rngOuter.Value
            strOutput = strOutput & rngInner.Offset(, -1).Value & ", "
            Set rngInner = rngInner.Offset(1, 0)
         Loop
         
         'strip out end comma and trailing space
         strOutput = Left(strOutput, Len(strOutput) - 2)


         'find the corresponding "X" value
         strFind = rngOuter.Value & "x"


         'in case the string is not found
         On Error Resume Next
            With Sheets("Sheet1")
               Set rngFound = .Columns(2).Find(What:=strFind, _
                  After:=.Cells(1, 2), _
                  LookIn:=xlValues, _
                  LookAt:=xlPart, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlNext, _
                  MatchCase:=False, _
                  SearchFormat:=False)
             End With
         On Error GoTo 0


         'finish off the output string and output the file
         If Not rngFound Is Nothing Then
            strOutput = strOutput & " from " & rngFound.Offset(, -1).Value & "."
            OutputFile strOutput
         End If
      
      Else
         'all done
         Exit Do
      End If
      
      'reset variables
      strOutput = ""
      
      'next row
      Set rngOuter = rngInner
   Loop
   
   'tidy up
   Set rngInner = Nothing
   Set rngOuter = Nothing
   Set rngFound = Nothing
End Sub




Private Sub OutputFile(ByVal myString As String)
   Dim fileName As String
   Dim fNum As Long


   fileName = "c:\temp\test.txt"
   fNum = FreeFile()
   
   Open fileName For Append As #fNum
   Print #fNum, myString
   Close #fNum
End Sub