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]

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
Why, when I run the following VBA code in another sheet, the value of the formula cell shows a !value# error.

VBA code:

Option Explicit


Sub Filtering_Country()
'
' Filtering Macro
'
Dim Country As String

Country = Worksheets("Keywords Analysis").Range("D1").Value

With Worksheets("Projects")
With .Range("AF2:AF" & .Cells(.Rows.Count, "R").End(xlUp).Row)
.AutoFilter 'Turn off any previous filtering
.AutoFilter Field:=32, Criteria1:=Country
End With
.AutoFilterMode = False
End With
End Sub

Formula in C2 in another sheet:

=SORT(TRIM(ItemList(Projects!AI2:AI2100,FALSE)))

 

[Peter_SSs]

When posting vba code in the forum, please use the available vba code tags, not the Quote tags. My signature block below has more details.

When posting a worksheet formula in the forum, please use the Formula tags, not the Quote tags

In your vba code you are filtering for Country but then immediately removing that filter. What is the point of that?

What row are your headings in on sheet 'Projects'?

 

[shafiey]

When posting vba code in the forum, please use the available vba code tags, not the Quote tags. My signature block below has more details.

View attachment 98853

When posting a worksheet formula in the forum, please use the Formula tags, not the Quote tags

View attachment 98854

In your vba code you are filtering for Country but then immediately removing that filter. What is the point of that?

View attachment 98852


What row are your headings in on sheet 'Projects'?

They are in the first row.

 

[Peter_SSs]

They are in the first row.

Thanks. With that arrangement, the code you posted in post #51 is not working for me.

What is the last column with a heading in row 1?

You also did not address this:

In your vba code you are filtering for Country but then immediately removing that filter. What is the point of that?

 

[shafiey]

Thanks. With that arrangement, the code you posted in post #51 is not working for me.

What is the last column with a heading in row 1?

You also did not address this:

1. The last column is AQ, its title is in Persian: کل کلمات کلیدی
2. In your vba code you are filtering for Country but then immediately removing that filter. What is the point of that?
I got the code from the internet and I don't know the details unfortunately.

 

[shafiey]

Thanks. With that arrangement, the code you posted in post #51 is not working for me.

What is the last column with a heading in row 1?

You also did not address this:

I want to filter the AF column in the projects sheet based on a value in another sheet (Keywords Analysis) in cell D1 that can be changed.
When the filter operation is done. The values in the third sheet (keywords) that have following formula show the value error.
=SORT(TRIM(ItemList(Projects!AI2:AI2100,FALSE)))

 

[Peter_SSs]

1. The last column is AQ, its title is in Persian: کل کلمات کلیدی
2. In your vba code you are filtering for Country but then immediately removing that filter. What is the point of that?
I got the code from the internet and I don't know the details unfortunately.

Ok, give this a try instead of the previous filtering code with a copy of your workbook.

Sub Filtering_Country_v2()
  '
  ' Filtering Macro
  '
  Dim Country As String
  
  Country = Worksheets("Keywords Analysis").Range("D1").Value
  Application.Calculation = xlCalculationManual
  With Worksheets("Projects")
    .AutoFilterMode = False
    .Range("A1:AQ" & .Cells(.Rows.Count, "AF").End(xlUp).Row).AutoFilter Field:=32, Criteria1:=Country
  End With
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[shafiey]

Ok, give this a try instead of the previous filtering code with a copy of your workbook.

Sub Filtering_Country_v2()
  '
  ' Filtering Macro
  '
  Dim Country As String
 
  Country = Worksheets("Keywords Analysis").Range("D1").Value
  Application.Calculation = xlCalculationManual
  With Worksheets("Projects")
    .AutoFilterMode = False
    .Range("A1:AQ" & .Cells(.Rows.Count, "AF").End(xlUp).Row).AutoFilter Field:=32, Criteria1:=Country
  End With
  Application.Calculation = xlCalculationAutomatic
End Sub

It worked Thank you very very very much

 

[shafiey]

Ok, give this a try instead of the previous filtering code with a copy of your workbook.

Sub Filtering_Country_v2()
  '
  ' Filtering Macro
  '
  Dim Country As String
 
  Country = Worksheets("Keywords Analysis").Range("D1").Value
  Application.Calculation = xlCalculationManual
  With Worksheets("Projects")
    .AutoFilterMode = False
    .Range("A1:AQ" & .Cells(.Rows.Count, "AF").End(xlUp).Row).AutoFilter Field:=32, Criteria1:=Country
  End With
  Application.Calculation = xlCalculationAutomatic
End Sub

Is it possible to write the VBA code as a UDF to automatically perform filtering by changing the value of cell D1?
Of course, if cell D1 is empty, filtering is not done. In other words, when cell D1 is empty, do not filter for empty cells.

 

[shafiey]

It worked Thank you very very very much

Ok, give this a try instead of the previous filtering code with a copy of your workbook.

Sub Filtering_Country_v2()
  '
  ' Filtering Macro
  '
  Dim Country As String
 
  Country = Worksheets("Keywords Analysis").Range("D1").Value
  Application.Calculation = xlCalculationManual
  With Worksheets("Projects")
    .AutoFilterMode = False
    .Range("A1:AQ" & .Cells(.Rows.Count, "AF").End(xlUp).Row).AutoFilter Field:=32, Criteria1:=Country
  End With
  Application.Calculation = xlCalculationAutomatic
End Sub

Is it possible to write the code in such a way that when the value of cell D1 is changed, the VBA code is automatically executed and the same sheet is refreshed.
Please accept my sincere thanks for taking the time for me.