Stack multi columns in one column for visible cells only

[shafiey]

Why doesn't the formula work in column B when I change the range from A2:A1012 to A2:A2100?​

Formula in B2:
=IFERROR(SORT(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN("، ",,IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A1012)-ROW(A2),)),A2:A1012,"")),"، ","</s><s>") & "</s></t>","//s")),"")

Formula in C2:
=IFERROR(SORT(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN("، ",,IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A2100)-ROW(A2),)),A2:A2100,"")),"، ","</s><s>") & "</s></t>","//s")),"")

Why doesn't the formula work in column B when I change the range from A2:A1012 to A2:A2100?

My File

Thank you very much

 

[shafiey]

Either way, you could give this a try.
I have assumed that the data is on Sheet1 and results go onto Sheet2 which exists but has nothing on it to start with.

On Sheet1 add the formula shown in C2 and copy down at least to the bottom of the data. Doesn't matter if the formula goes further as I have done.

shafiey.xlsm
	A	B	C
1	keywords	keywords02
2	a	h	2
3	h,k	d, i, g	2
4	a	h, h	2
5	b, d, g, h, i	b, e, b	2
6	g	d	2
7			0
8			0
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SUBTOTAL(103,A2:B2)

Now run the following macro. Post back if you need instructions about that.

Sub Make_List()
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  With Sheets("Sheet1")
    a = .Range("A2:C" & .Columns("A:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value
  End With
  With CreateObject("System.Collections.ArrayList")
    For i = 1 To UBound(a)
      If a(i, 3) > 0 Then
        For Each itm In Split(Replace(Application.TextJoin(",", 1, a(i, 1), a(i, 2)), " ", ""), ",")
          If Not .Contains(itm) Then .Add itm
        Next itm
      End If
    Next i
    .Sort
    If .Count > 0 Then b = Application.Transpose(.ToArray)
  End With
  With Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    .Value = .Offset(, -1).Value + 1
    If Not IsEmpty(b) Then .Offset(1).Resize(UBound(b) - LBound(b) + 1).Value = b
  End With
End Sub

Filter the data differently and run the macro again.
Each time the macro is run, the results are written to Sheet2 in a new column (the location can be changed to suit your requirements).
Here is my Sheet2 after I have run the macro 5 times. The top row just counts the number of times the macro has been run & could be removed from the code if not required.

1. First run was with nothing filtered out.
2. Second run I filtered out the "a" in column A
3. Third run I returned column A to Show All and filtered column B to show "d" only
4. Fourth run I returned both columns to Show All but then applied a text filter to show "x" in column B. This meant all rows except the heading row were hidden.
5. Fifth run I returned both columns to Show All

shafiey.xlsm
	A	B	C	D	E	F
1		1	2	3	4	5
2		a	b	d		a
3		b	d	g		b
4		d	e			d
5		e	g			e
6		g	h			g
7		h	i			h
8		i	k			i
9		k				k
10
Sheet2

That's OK. very very thanks
The extendoffice.com site has also put the following code on their site, which solves the problem.
Is it possible to re-edit the code so that the repetitions are also given in the column? similar to the image below:

Sub Vertical()
'UpdatebyExtendoffice20161125
Dim i As Long, strTxt As String
Dim startP As Range
Dim xRg As Range, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox _
(Prompt:="Range Selection...", _
Title:="Kutools For Excel", Type:=8)
i = 1
Application.ScreenUpdating = False
For Each yRg In xRg
If i = 1 Then
strTxt = yRg.Text
i = 2
Else
strTxt = strTxt & "¡ " & yRg.Text
End If
Next
Application.ScreenUpdating = True
Set startP = Application.InputBox _
(Prompt:="paste range...", _
Title:="Kutools For Excel", Type:=8)
ary = Split(strTxt, "¡ ")
i = 1
Application.ScreenUpdating = False
For Each a In ary
startP(i, 1).Value = a
i = i + 1
Next a
Application.ScreenUpdating = True
End Sub

 

[Anonymous1378]

wow, That's ok. very very great, your formula worked.
Thank you very much.
Thank you very much for taking the time for me.
Is it possible to include repetitions in the column? Same as the following image:

Just remove the UNIQUE() function from the last row.

Slightly surprised you had no issues using it, but all the better, I guess.

 

[Peter_SSs]

Can you please stop quoting the same thing multiple times? I have just removed 3 identical quotes from your last two posts!

There is also no need to keep asking the same question as you have done at the bottom of posts 20 and 21.

wow, That's ok. very very great, your formula worked.

As you will have gathered from the discussion in the thread, that formula works on your small sample data, but it almost certainly will not work reliably on the large data that you said you had.
To be sure, you would need to check manually that nothing had been missed - and that would defeat the purpose of having a formula in the first place.

The extendoffice.com site has also put the following code on their site, which solves the problem.

If you are using code from another site, perhaps you should ask them about modifications.

Did you try the method that I suggested in post 16?

 

[shafiey]

Can you please stop quoting the same thing multiple times? I have just removed 3 identical quotes from your last two posts!

There is also no need to keep asking the same question as you have done at the bottom of posts 20 and 21.

As you will have gathered from the discussion in the thread, that formula works on your small sample data, but it almost certainly will not work reliably on the large data that you said you had.
To be sure, you would need to check manually that nothing had been missed - and that would defeat the purpose of having a formula in the first place.


If you are using code from another site, perhaps you should ask them about modifications.

Did you try the method that I suggested in post 16?

Yes, I run your vba code, but it takes a long time. My system RAM is 6GB and I have run it since 17:05 for 2100 rows but till now 17:24 is processing.

 

[shafiey]

Just remove the UNIQUE() function from the last row.

Slightly surprised you had no issues using it, but all the better, I guess.

How can I expand the formula for more columns, for example 12 columns?

 

[shafiey]

Can you please stop quoting the same thing multiple times? I have just removed 3 identical quotes from your last two posts!

There is also no need to keep asking the same question as you have done at the bottom of posts 20 and 21.

As you will have gathered from the discussion in the thread, that formula works on your small sample data, but it almost certainly will not work reliably on the large data that you said you had.
To be sure, you would need to check manually that nothing had been missed - and that would defeat the purpose of having a formula in the first place.


If you are using code from another site, perhaps you should ask them about modifications.

Did you try the method that I suggested in post 16?

I tested your vba code for 1936 rows with 362 words with different iterations, unfortunately it returned only 129 words.

 

[Anonymous1378]

How can I expand the formula for more columns, for example 12 columns?

Change the 2 in variable b to COLUMNS(a).

 

[shafiey]

Just remove the UNIQUE() function from the last row.

Slightly surprised you had no issues using it, but all the better, I guess.

When I delete some cells in columns A to D, the results of the number of repetitions in column E return wrong.

My Test file

test.xlsx
	A	B	C	D	E	F
1	Col01	Col02	Col03	Col04	Duplicates	without Duplicates
2	a	b, c	d, e, f	g, h, i, j	a	a
3	a		d, e, f	g, h, i, j	a	b
4	a	b, c	d, e, f	g, h, i, j	a	c
5	a	b, c	d, e, f	g, h, i, j	a	d
6	a	b, c	d, e, f	g, h, i, j	a	e
7	a	b, c	d, e, f	g, h, i, j	a	f
8	a	b, c	d, e, f	g, h, i, j	a	g
9	a	b, c	d, e, f	g, h, i, j	a	h
10	a	b, c	d, e, f	g, h, i, j	a	i
11	a	b, c	d, e, f	g, h, i, j	a	j
12					b	#VALUE!
13					b
14					b
15					b
16					b
17					b
18					b
19					b
20					c
21					c
22					c
23					c
24					c
25					c
26					c
27					c
28					d
29					d
30					d
31					d
32					d
33					d
34					d
35					d
36					d
37					d
38					e
39					e
40					e
41					e
42					e
43					e
44					e
45					e
46					e
47					e
48					f
49					f
50					f
51					f
52					f
53					f
54					f
55					f
56					f
57					f
58					g
59					g
60					g
61					g
62					g
63					g
64					g
65					g
66					g
67					g
68					h
69					h
70					h
71					h
72					h
73					h
74					h
75					h
76					h
77					h
78					i
79					i
80					i
81					i
82					i
83					i
84					i
85					i
86					i
87					i
88					j
89					j
90					j
91					j
92					j
93					j
94					j
95					j
96					j
97					j
98					#VALUE!
99					#VALUE!
100
Sheet1

Cell Formulas
Range	Formula
E2:E99	E2	=LET( a,FILTER(A2:D20,SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),0,1))), b,INDEX(a,MOD(SEQUENCE(COUNTA(a))-1,ROWS(a))+1,MOD(SEQUENCE(COUNTA(a))-1,4)+1), c,LEN(b)-LEN(SUBSTITUTE(b,",",""))+1, d,MMULT(--(SEQUENCE(ROWS(c))>=TRANSPOSE(SEQUENCE(ROWS(c)))),c), e,SEQUENCE(MAX(d)), f,XLOOKUP(e,d,d,,1), g,XMATCH(e,d,1), SORT(FILTERXML("<t><s>" & SUBSTITUTE(INDEX(b,g),",","</s><s>") & "</s></t>","//s["&f-e+1&"]")))
F2:F12	F2	=LET( a,FILTER(A2:D20,SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),0,1))), b,INDEX(a,MOD(SEQUENCE(COUNTA(a))-1,ROWS(a))+1,MOD(SEQUENCE(COUNTA(a))-1,4)+1), c,LEN(b)-LEN(SUBSTITUTE(b,",",""))+1, d,MMULT(--(SEQUENCE(ROWS(c))>=TRANSPOSE(SEQUENCE(ROWS(c)))),c), e,SEQUENCE(MAX(d)), f,XLOOKUP(e,d,d,,1), g,XMATCH(e,d,1), SORT(UNIQUE(FILTERXML("<t><s>" & SUBSTITUTE(INDEX(b,g),",","</s><s>") & "</s></t>","//s["&f-e+1&"]"))))
Dynamic array formulas.

 

[Anonymous1378]

You have not encountered this issue yet, but it cannot handle blanks in column A; by nature of using SUBTOTAL(3). I have no idea how that would be worked around.
As for this error, wrapping FILTERXML with IFERROR(,"") should suffice.

 

[shafiey]

You have not encountered this issue yet, but it cannot handle blanks in column A; by nature of using SUBTOTAL(3). I have no idea how that would be worked around.
As for this error, wrapping FILTERXML with IFERROR(,"") should suffice.

I am a beginner and do not know how to write formulas. If possible, think of a solution for me. Thank you very much
The best formula I have found so far on the internet is yours.