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

kriszpm

New Member
Joined
Dec 19, 2018
Messages
6
-----------------------------------------------------------------------------------------------------------------------------------------------------------

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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

kriszpm

New Member
Joined
Dec 19, 2018
Messages
6
Forgot to add more info...

ideally, it needs to process a list of Strings
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

kriszpm

New Member
Joined
Dec 19, 2018
Messages
6
Thank you Very much for the Script.

Is it possible to make them into a single column List?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Jun 29, 2014
Messages
5,146
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,958
Messages
5,447,565
Members
405,456
Latest member
melmelseh

This Week's Hot Topics

Top