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

[kriszpm]

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

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.

 

[kriszpm]

Forgot to add more info...

ideally, it needs to process a list of Strings

 

[MickG]

Try this for strings in column "A" starting "A2", with result in subsequent columns for each string, starting "B2".

[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]

Thank you Very much for the Script.

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

 

[MickG]

Try this for results in column "B".

[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]

Hi,

Formula solution options:

Book1
	A	B	C
1	calculating cumulative stats for quarters
2	calculating	calculating cumulative	calculating cumulative stats
3	cumulative	cumulative stats	cumulative stats for
4	stats	stats for	stats for quarters
5	for	for quarters
6	quarters
7
8
9	1 word	2 words	3 words
Sheet410

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1," ",REPT(" ",LEN(A$1))),(ROWS(A$2:A2)-1)*LEN(A$1)+1,LEN(A$1)))
B2	=IF(LEN(A$1)-LEN(SUBSTITUTE(A$1," ",""))A$2:A2),"",TRIM(MID(SUBSTITUTE(A$1," ",REPT(" ",LEN(A$1))),(ROWS(A$2:A2)-1)*LEN(A$1)+1,LEN(A$1)*2)))
C2	=IF(LEN(A$1)-LEN(SUBSTITUTE(A$1," ",""))<=ROWS(A$2:A2),"",TRIM(MID(SUBSTITUTE(A$1," ",REPT(" ",LEN(A$1))),(ROWS(A$2:A2)-1)*LEN(A$1)+1,LEN(A$1)*3)))

Formulas copied down.

 

[kriszpm]

Thank you very much...

 

[MickG]

You're welcome