Need Help on making a list of words based on the below requirement

kriszpm

New Member
-----------------------------------------------------------------------------------------------------------------------------------------------------------

Example String: "calculating cumulative stats for quarters"

-----------------------------------------------------------------------------------------------------------------------------------------------------------

The outcome should be in the below format:


1 Word List: (No Duplicates)
calculating
cumulative
stats
for
quarters



2 Words List: (The 2 Words should be only in sequence from left to right. No Combinations required. And No Duplicates)
calculating cumulative
cumulative stats
stats for
for quarters




3 Words List: (Same condition as 2 Words List. And No Duplicates)
calculating cumulative stats
cumulative stats for
stats for quarters

-----------------------------------------------------------------------------------------------------------------------------------------------------------


Thanks in advance.
 

MickG

MrExcel MVP
Try this for strings in column "A" starting "A2", with result in subsequent columns for each string, starting "B2".
Code:
[COLOR="Navy"]Sub[/COLOR] MG19Dec29
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tim [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
col = 1

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
col = col + 1: c = 2
Sp = Split(Dn.Value, " ")
Cells(c, col).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)

c = UBound(Sp) + 5
[COLOR="Navy"]For[/COLOR] Tim = 1 To 2
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp) - Tim
    c = c + 1
    [COLOR="Navy"]For[/COLOR] Ac = n To n + Tim
        Cells(c, col) = Cells(c, col) & " " & Sp(Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
c = c + 3
[COLOR="Navy"]Next[/COLOR] Tim
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

MickG

MrExcel MVP
Try this for results in column "B".
Code:
[COLOR="Navy"]Sub[/COLOR] MG19Dec34
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tim [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Range("B:B").ClearContents
c = 2

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, " ")
Cells(c, "B").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
    c = c + UBound(Sp) + 5
    [COLOR="Navy"]For[/COLOR] Tim = 1 To 2
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp) - Tim
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = n To n + Tim
                Cells(c, "B") = Cells(c, "B") & " " & Sp(Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] n
        c = c + 3
    [COLOR="Navy"]Next[/COLOR] Tim
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

jtakw

Well-known Member
Hi,

Formula solution options:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">calculating cumulative stats for quarters</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">calculating</td><td style=";">calculating cumulative</td><td style=";">calculating cumulative stats</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">cumulative</td><td style=";">cumulative stats</td><td style=";">cumulative stats for</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">stats</td><td style=";">stats for</td><td style=";">stats for quarters</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">for</td><td style=";">for quarters</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">quarters</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">1 word</td><td style=";">2 words</td><td style=";">3 words</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet410</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">A$1," ",REPT(<font color="Purple">" ",LEN(<font color="Teal">A$1</font>)</font>)</font>),(<font color="Green">ROWS(<font color="Purple">A$2:A2</font>)-1</font>)*LEN(<font color="Green">A$1</font>)+1,LEN(<font color="Green">A$1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A$1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A$1," ",""</font>)</font>)<ROWS(<font color="Red">A$2:A2</font>),"",TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">A$1," ",REPT(<font color="Teal">" ",LEN(<font color="#FF00FF">A$1</font>)</font>)</font>),(<font color="Purple">ROWS(<font color="Teal">A$2:A2</font>)-1</font>)*LEN(<font color="Purple">A$1</font>)+1,LEN(<font color="Purple">A$1</font>)*2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A$1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A$1," ",""</font>)</font>)<=ROWS(<font color="Red">A$2:A2</font>),"",TRIM(<font color="Red">MID(<font color="Green">SUBSTITUTE(<font color="Purple">A$1," ",REPT(<font color="Teal">" ",LEN(<font color="#FF00FF">A$1</font>)</font>)</font>),(<font color="Purple">ROWS(<font color="Teal">A$2:A2</font>)-1</font>)*LEN(<font color="Purple">A$1</font>)+1,LEN(<font color="Purple">A$1</font>)*3</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Formulas copied down.
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top