Stack multi columns in one column for visible cells only

shafiey · Sep 6, 2023

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 · Sep 8, 2023

In the sample below, I have filled in the blank cell B3 and changed the yellow cell as shown.
I have also reduced the ranges so that no blank cells are involved.
If you do that what results do you get? As you can see, for me the results are missing the "x" in both lists.

shafiey.xlsm

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

3

a

b, c

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,x, j

a

i

11

a

b, c

d, e, f

g, h, i, j

a

j

12

b

13

b

14

b

15

b

16

b

17

b

18

b

19

b

20

b

21

b

22

c

23

c

24

c

25

c

26

c

27

c

28

c

29

c

30

c

31

c

32

d

33

d

34

d

35

d

36

d

37

d

38

d

39

d

40

d

41

d

42

e

43

e

44

e

45

e

46

e

47

e

48

e

49

e

50

e

51

e

52

f

53

f

54

f

55

f

56

f

57

f

58

f

59

f

60

f

61

f

62

g

63

g

64

g

65

g

66

g

67

g

68

g

69

g

70

g

71

g

72

h

73

h

74

h

75

h

76

h

77

h

78

h

79

h

80

h

81

h

82

i

83

i

84

i

85

i

86

i

87

i

88

i

89

i

90

i

91

i

92

j

93

j

94

j

95

j

96

j

97

j

98

j

99

j

100

j

101

j

102

Sheet4

Cell Formulas
Range	Formula
E2:E101	E2	=LET( a,FILTER(A2:D11,SUBTOTAL(3,OFFSET(A2,ROW(A2:A11)-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:F11	F2	=LET( a,FILTER(A2:D11,SUBTOTAL(3,OFFSET(A2,ROW(A2:A11)-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.

shafiey · Sep 8, 2023

Peter_SSs said:
In the sample below, I have filled in the blank cell B3 and changed the yellow cell as shown.
I have also reduced the ranges so that no blank cells are involved.
If you do that what results do you get? As you can see, for me the results are missing the "x" in both lists.

shafiey.xlsm
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 b, c 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,x, j a i
11 a b, c d, e, f g, h, i, j a j
12 b
13 b
14 b
15 b
16 b
17 b
18 b
19 b
20 b
21 b
22 c
23 c
24 c
25 c
26 c
27 c
28 c
29 c
30 c
31 c
32 d
33 d
34 d
35 d
36 d
37 d
38 d
39 d
40 d
41 d
42 e
43 e
44 e
45 e
46 e
47 e
48 e
49 e
50 e
51 e
52 f
53 f
54 f
55 f
56 f
57 f
58 f
59 f
60 f
61 f
62 g
63 g
64 g
65 g
66 g
67 g
68 g
69 g
70 g
71 g
72 h
73 h
74 h
75 h
76 h
77 h
78 h
79 h
80 h
81 h
82 i
83 i
84 i
85 i
86 i
87 i
88 i
89 i
90 i
91 i
92 j
93 j
94 j
95 j
96 j
97 j
98 j
99 j
100 j
101 j
102
Sheet4
Cell Formulas
Range Formula
E2:E101 E2 =LET( a,FILTER(A2:D11,SUBTOTAL(3,OFFSET(A2,ROW(A2:A11)-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:F11 F2 =LET( a,FILTER(A2:D11,SUBTOTAL(3,OFFSET(A2,ROW(A2:A11)-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.

Thank you very much for your attention. I had not thought of this.
In this way, I think the formula has a lot of work to do?
Is there any other solution?

Peter_SSs · Sep 8, 2023

shafiey said:
I had not thought of this.

It was mentioned way back in posts 18 & 19

Peter_SSs said:
... more than 2,000 words were missing from the results.

Anonymous1378 said:
about 500 out of 4,300 words missing.

shafiey said:
Is there any other solution?

Yes, vba, also mentioned before. This time I have used a User-Defined Function (UDF). I have still also employed an extra column. This could be achieved with vba and without the helper column but the function would be slower to calculate with large ranges. Post back if you want that option though.

Data sheet with the helper column

shafiey.xlsm

A

B

C

D

E

1

Col01

Col02

Col03

Col04

2

a

b, c

d, e, f

g, h, i, j

4

3

a

d, e, f

g, h, i, j

3

4

a

b, c

d, e, f

g, h, i, j

4

5

0

6

a

b, c

d, e, f

g, h, i, j

4

7

a

b, c

d, e, f

g, h, i, j

4

8

b, c

d, e, f

g, h, i, j

3

9

a

b, c

d, e, f

g, h, i, j

4

10

a

b, c

d, e, f

g, h,x, j

4

11

a

b, c

d, e, f

g, h, i, j

4

Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=SUBTOTAL(103,A2:D2)

To implement the UDF
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below (top cell in each column only)
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:

Function ListItems(r As Range, Optional RemoveDupes As Boolean = False) As Variant
  Dim a As Variant, itm As Variant
  Dim i As Long, cols As Long
 
  a = r.Value
  cols = UBound(a, 2)
  With CreateObject("System.Collections.ArrayList")
    For i = 1 To UBound(a)
      If a(i, cols) > 0 Then
        For Each itm In Split(Replace(Application.TextJoin(",", 1, Application.Index(a, i, Application.Sequence(cols - 1))), " ", ""), ",")
          If RemoveDupes Then
            If Not .Contains(itm) Then .Add itm
          Else
            .Add itm
          End If
        Next itm
      End If
    Next i
    .Sort
    If .Count > 0 Then ListItems = Application.Transpose(.ToArray)
  End With
End Function

shafiey_2.xlsm

A

B

1

Duplicates

without Duplicates

2

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	=ListItems(Sheet1!A2:E11,FALSE)
B2:B12	B2	=ListItems(Sheet1!A2:E11,TRUE)
Dynamic array formulas.

shafiey · Sep 8, 2023

Peter_SSs said:
It was mentioned way back in posts 18 & 19

Yes, vba, also mentioned before. This time I have used a User-Defined Function (UDF). I have still also employed an extra column. This could be achieved with vba and without the helper column but the function would be slower to calculate with large ranges. Post back if you want that option though.

Data sheet with the helper column

shafiey.xlsm
A B C D E
1 Col01 Col02 Col03 Col04
2 a b, c d, e, f g, h, i, j 4
3 a d, e, f g, h, i, j 3
4 a b, c d, e, f g, h, i, j 4
5 0
6 a b, c d, e, f g, h, i, j 4
7 a b, c d, e, f g, h, i, j 4
8 b, c d, e, f g, h, i, j 3
9 a b, c d, e, f g, h, i, j 4
10 a b, c d, e, f g, h,x, j 4
11 a b, c d, e, f g, h, i, j 4
Sheet1
Cell Formulas
Range Formula
E2:E11 E2 =SUBTOTAL(103,A2:D2)

To implement the UDF
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below (top cell in each column only)
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:

Function ListItems(r As Range, Optional RemoveDupes As Boolean = False) As Variant Dim a As Variant, itm As Variant Dim i As Long, cols As Long a = r.Value cols = UBound(a, 2) With CreateObject("System.Collections.ArrayList") For i = 1 To UBound(a) If a(i, cols) > 0 Then For Each itm In Split(Replace(Application.TextJoin(",", 1, Application.Index(a, i, Application.Sequence(cols - 1))), " ", ""), ",") If RemoveDupes Then If Not .Contains(itm) Then .Add itm Else .Add itm End If Next itm End If Next i .Sort If .Count > 0 Then ListItems = Application.Transpose(.ToArray) End With End Function

shafiey.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 a i
11 b j
12 b x
13 b
14 b
15 b
16 b
17 b
18 b
19 c
20 c
21 c
22 c
23 c
24 c
25 c
26 c
27 d
28 d
29 d
30 d
31 d
32 d
33 d
34 d
35 d
36 e
37 e
38 e
39 e
40 e
41 e
42 e
43 e
44 e
45 f
46 f
47 f
48 f
49 f
50 f
51 f
52 f
53 f
54 g
55 g
56 g
57 g
58 g
59 g
60 g
61 g
62 g
63 h
64 h
65 h
66 h
67 h
68 h
69 h
70 h
71 h
72 i
73 i
74 i
75 i
76 i
77 i
78 i
79 i
80 j
81 j
82 j
83 j
84 j
85 j
86 j
87 j
88 j
89 x
90
Sheet2
Cell Formulas
Range Formula
A2:A89 A2 =ListItems(Sheet1!A2:E11,FALSE)
B2:B12 B2 =ListItems(Sheet1!A2:E11,TRUE)
Dynamic array formulas.

I did all the steps you said, but it gives an error. I don't know which part I did wrong. I have sent the file, please check it if possible.
MyFile

Peter_SSs · Sep 8, 2023

You have not entered the formulas in the helper column. See column E in the first mini sheet in post 33.

Also, it would not be a good idea to put the formula beside the data like that. If you filter the original data to hide some rows, it will also hide some rows of the results.

shafiey · Sep 8, 2023

Peter_SSs said:
You have not entered the formulas in the helper column. See column E in the first mini sheet in post 33.

Also, it would not be a good idea to put the formula beside the data like that. If you filter the original data to hide some rows, it will also hide some rows of the results.

I corrected it, but it does not show the results.
File02

Peter_SSs · Sep 8, 2023

shafiey said:
I corrected it

No you haven't. You have not entered the formulas in column E.

shafiey · Sep 8, 2023

Peter_SSs said:
No you haven't. You have not entered the formulas in column E.

View attachment 98424

wowwwww

, very very good. I tested the formula and all the results were correct. The processing speed was also excellent. My problem was solved. Thank you very much for your efforts. I wish you success and health.

Peter_SSs · Sep 8, 2023

You're welcome. Glad we seem to have got there in the end.

shafiey · Sep 8, 2023

Peter_SSs said:
It was mentioned way back in posts 18 & 19

Yes, vba, also mentioned before. This time I have used a User-Defined Function (UDF). I have still also employed an extra column. This could be achieved with vba and without the helper column but the function would be slower to calculate with large ranges. Post back if you want that option though.

Data sheet with the helper column

shafiey.xlsm
A B C D E
1 Col01 Col02 Col03 Col04
2 a b, c d, e, f g, h, i, j 4
3 a d, e, f g, h, i, j 3
4 a b, c d, e, f g, h, i, j 4
5 0
6 a b, c d, e, f g, h, i, j 4
7 a b, c d, e, f g, h, i, j 4
8 b, c d, e, f g, h, i, j 3
9 a b, c d, e, f g, h, i, j 4
10 a b, c d, e, f g, h,x, j 4
11 a b, c d, e, f g, h, i, j 4
Sheet1
Cell Formulas
Range Formula
E2:E11 E2 =SUBTOTAL(103,A2:D2)

To implement the UDF
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below (top cell in each column only)
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:

Function ListItems(r As Range, Optional RemoveDupes As Boolean = False) As Variant Dim a As Variant, itm As Variant Dim i As Long, cols As Long a = r.Value cols = UBound(a, 2) With CreateObject("System.Collections.ArrayList") For i = 1 To UBound(a) If a(i, cols) > 0 Then For Each itm In Split(Replace(Application.TextJoin(",", 1, Application.Index(a, i, Application.Sequence(cols - 1))), " ", ""), ",") If RemoveDupes Then If Not .Contains(itm) Then .Add itm Else .Add itm End If Next itm End If Next i .Sort If .Count > 0 Then ListItems = Application.Transpose(.ToArray) End With End Function

shafiey.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 a i
11 b j
12 b x
13 b
14 b
15 b
16 b
17 b
18 b
19 c
20 c
21 c
22 c
23 c
24 c
25 c
26 c
27 d
28 d
29 d
30 d
31 d
32 d
33 d
34 d
35 d
36 e
37 e
38 e
39 e
40 e
41 e
42 e
43 e
44 e
45 f
46 f
47 f
48 f
49 f
50 f
51 f
52 f
53 f
54 g
55 g
56 g
57 g
58 g
59 g
60 g
61 g
62 g
63 h
64 h
65 h
66 h
67 h
68 h
69 h
70 h
71 h
72 i
73 i
74 i
75 i
76 i
77 i
78 i
79 i
80 j
81 j
82 j
83 j
84 j
85 j
86 j
87 j
88 j
89 x
90
Sheet2
Cell Formulas
Range Formula
A2:A89 A2 =ListItems(Sheet1!A2:E11,FALSE)
B2:B12 B2 =ListItems(Sheet1!A2:E11,TRUE)
Dynamic array formulas.

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

Stack multi columns in one column for visible cells only

shafiey

Board Regular

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

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

shafiey

Board Regular

Peter_SSs

MrExcel MVP, Moderator

shafiey

Board Regular

Peter_SSs

MrExcel MVP, Moderator

shafiey

Board Regular

Peter_SSs

MrExcel MVP, Moderator

shafiey

Board Regular

Peter_SSs

MrExcel MVP, Moderator

shafiey

Board Regular

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
A2:A89	A2	=ListItems(Sheet1!A2:E11,FALSE)
B2:B12	B2	=ListItems(Sheet1!A2:E11,TRUE)
Dynamic array formulas.

Stack multi columns in one column for visible cells only

Board Regular

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

MrExcel MVP, Moderator

Excel Facts

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

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