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

 

[Peter_SSs]

In post 33, you mentioned that it is possible without the helper column, but its speed is lower. I would be grateful if you could send me that method so that I can test it. Thanks

Here is another UDF that does that. I doubt you will notice any difference in speed with the sample data but with thousands of rows and multiple columns with multiple words you may well see a slowness.

Function ItemList(r As Range, Optional RemoveDupes As Boolean = False) As Variant
  Dim AL As Object
  Dim itm As Variant
  Dim c As Range, rw As Range

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each rw In r.Rows
    If Not rw.EntireRow.Hidden Then
      For Each c In rw.Cells
        For Each itm In Split(Replace(c.Value, " ", ""), ",")
          If RemoveDupes Then
            If Not AL.Contains(itm) Then AL.Add itm
          Else
            AL.Add itm
          End If
        Next itm
      Next c
    End If
  Next rw
  AL.Sort
  If AL.Count > 0 Then ItemList = Application.Transpose(AL.ToArray)
End Function

Results (same Sheet1 data as before but no need for the helper column)

shafiey_2.xlsm
	A	B
1	Duplicates	without Duplicates
2	a	a
3	a	b
4	a	c
5	a	d
6	a	e
7	a	f
8	a	g
9	a	h
10	b	i
11	b	j
12	b	x
13	b
14	b
15	b
16	b
17	b
18	c
19	c
20	c
21	c
22	c
23	c
24	c
25	c
26	d
27	d
28	d
29	d
30	d
31	d
32	d
33	d
34	d
35	e
36	e
37	e
38	e
39	e
40	e
41	e
42	e
43	e
44	f
45	f
46	f
47	f
48	f
49	f
50	f
51	f
52	f
53	g
54	g
55	g
56	g
57	g
58	g
59	g
60	g
61	g
62	h
63	h
64	h
65	h
66	h
67	h
68	h
69	h
70	h
71	i
72	i
73	i
74	i
75	i
76	i
77	i
78	i
79	j
80	j
81	j
82	j
83	j
84	j
85	j
86	j
87	j
88	x
89
Sheet2

Cell Formulas
Range	Formula
A2:A88	A2	=ItemList(Sheet1!A2:D11,FALSE)
B2:B12	B2	=ItemList(Sheet1!A2:D11,TRUE)
Dynamic array formulas.

 

[shafiey]

Here is another UDF that does that. I doubt you will notice any difference in speed with the sample data but with thousands of rows and multiple columns with multiple words you may well see a slowness.

Function ItemList(r As Range, Optional RemoveDupes As Boolean = False) As Variant
  Dim AL As Object
  Dim itm As Variant
  Dim c As Range, rw As Range

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each rw In r.Rows
    If Not rw.EntireRow.Hidden Then
      For Each c In rw.Cells
        For Each itm In Split(Replace(c.Value, " ", ""), ",")
          If RemoveDupes Then
            If Not AL.Contains(itm) Then AL.Add itm
          Else
            AL.Add itm
          End If
        Next itm
      Next c
    End If
  Next rw
  AL.Sort
  If AL.Count > 0 Then ItemList = Application.Transpose(AL.ToArray)
End Function

Results (same Sheet1 data as before but no need for the helper column)

shafiey_2.xlsm
	A	B
1	Duplicates	without Duplicates
2	a	a
3	a	b
4	a	c
5	a	d
6	a	e
7	a	f
8	a	g
9	a	h
10	b	i
11	b	j
12	b	x
13	b
14	b
15	b
16	b
17	b
18	c
19	c
20	c
21	c
22	c
23	c
24	c
25	c
26	d
27	d
28	d
29	d
30	d
31	d
32	d
33	d
34	d
35	e
36	e
37	e
38	e
39	e
40	e
41	e
42	e
43	e
44	f
45	f
46	f
47	f
48	f
49	f
50	f
51	f
52	f
53	g
54	g
55	g
56	g
57	g
58	g
59	g
60	g
61	g
62	h
63	h
64	h
65	h
66	h
67	h
68	h
69	h
70	h
71	i
72	i
73	i
74	i
75	i
76	i
77	i
78	i
79	j
80	j
81	j
82	j
83	j
84	j
85	j
86	j
87	j
88	x
89
Sheet2

Cell Formulas
Range	Formula
A2:A88	A2	=ItemList(Sheet1!A2:D11,FALSE)
B2:B12	B2	=ItemList(Sheet1!A2:D11,TRUE)
Dynamic array formulas.

Oh my god, this one is great. This is exactly what I was looking for. Thank you for your kindness.

 

[Peter_SSs]

Have you tried it on the large data yet?

 

[shafiey]

Have you tried it on the large data yet?

Yes, I ran your VBA code on a column with 2100 rows and with different iterations (2-7 iterations per row) and the results were correct. The processing speed was also very high and within 1 second (one click).

 

[Peter_SSs]

Cheers. Glad it is working well for you.

 

[shafiey]

.

 

[shafiey]

Hello, I also tested the data using VSTAVK and TEXTSPLIT functions in Office 365 and the result was the same as your VBA code.

Cheers. Glad it is working well for you.

 

[Peter_SSs]

Hello, I also tested the data using VSTAVK and TEXTSPLIT functions in Office 365 and the result was the same as your VBA code.

Just be aware that (as per some of the previous examples) those results may not be reliable. And if your data is large any incorrect results will not be obvious.

 

[shafiey]

Here is another UDF that does that. I doubt you will notice any difference in speed with the sample data but with thousands of rows and multiple columns with multiple words you may well see a slowness.

Function ItemList(r As Range, Optional RemoveDupes As Boolean = False) As Variant
  Dim AL As Object
  Dim itm As Variant
  Dim c As Range, rw As Range

  Set AL = CreateObject("System.Collections.ArrayList")
  For Each rw In r.Rows
    If Not rw.EntireRow.Hidden Then
      For Each c In rw.Cells
        For Each itm In Split(Replace(c.Value, " ", ""), ",")
          If RemoveDupes Then
            If Not AL.Contains(itm) Then AL.Add itm
          Else
            AL.Add itm
          End If
        Next itm
      Next c
    End If
  Next rw
  AL.Sort
  If AL.Count > 0 Then ItemList = Application.Transpose(AL.ToArray)
End Function

Results (same Sheet1 data as before but no need for the helper column)

shafiey_2.xlsm
	A	B
1	Duplicates	without Duplicates
2	a	a
3	a	b
4	a	c
5	a	d
6	a	e
7	a	f
8	a	g
9	a	h
10	b	i
11	b	j
12	b	x
13	b
14	b
15	b
16	b
17	b
18	c
19	c
20	c
21	c
22	c
23	c
24	c
25	c
26	d
27	d
28	d
29	d
30	d
31	d
32	d
33	d
34	d
35	e
36	e
37	e
38	e
39	e
40	e
41	e
42	e
43	e
44	f
45	f
46	f
47	f
48	f
49	f
50	f
51	f
52	f
53	g
54	g
55	g
56	g
57	g
58	g
59	g
60	g
61	g
62	h
63	h
64	h
65	h
66	h
67	h
68	h
69	h
70	h
71	i
72	i
73	i
74	i
75	i
76	i
77	i
78	i
79	j
80	j
81	j
82	j
83	j
84	j
85	j
86	j
87	j
88	x
89
Sheet2

Cell Formulas
Range	Formula
A2:A88	A2	=ItemList(Sheet1!A2:D11,FALSE)
B2:B12	B2	=ItemList(Sheet1!A2:D11,TRUE)
Dynamic array formulas.

Hello, in this function, how can the resulting data be sorted in the column.
I added the sort function to the formula, but it didn't work:
=SORT(ItemList(D2:D6000),FALSE))

 

[shafiey]

I apologize, it was a mistake. The function itself has a sort.