Count continuous “X’s” and covert the numbers into “N.X”

Kishan · May 15, 2023

Using Excel 2000

Hi,

Data range B23:AO36 each row has numbers and text “X”, I want to Count continuous “X’s” like shown 1, 2, 3, and 4…and so on, covert numbers 1 or 2 into “N.X” And put the result in range B5:AO18 as shown in example sheet below.

Please suggest a VBA solution.

NX.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

1

2

3

4

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

5

1

N.X

1

N.X

1

N.X

1

N.X

6

2

N.X

7

3

N.X

1

2

N.X

1

N.X

1

N.X

8

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

9

5

N.X

1

N.X

1

2

3

4

N.X

10

6

N.X

1

N.X

1

N.X

1

N.X

11

7

1

N.X

1

N.X

1

2

N.X

1

N.X

12

8

1

N.X

1

N.X

1

N.X

13

9

N.X

1

N.X

1

N.X

1

14

10

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

15

11

1

N.X

1

2

N.X

1

2

N.X

16

12

1

2

N.X

1

2

N.X

1

N.X

1

2

N.X

17

13

1

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

N.X

1

18

14

N.X

1

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

N.X

19

20

21

22

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

23

1

X

1

X

1

X

1

2

1

24

2

1

2

1

2

1

2

25

3

1

2

X

2

1

2

1

2

X

1

X

2

26

4

1

2

1

X

1

X

1

2

1

X

1

X

27

5

1

2

1

2

1

X

1

2

X

1

28

6

2

1

X

1

X

1

2

1

X

2

1

2

1

29

7

X

1

X

1

2

1

2

1

2

X

2

1

X

2

1

2

1

30

8

X

1

2

1

2

1

X

2

1

2

1

2

X

1

31

9

1

2

1

2

1

X

2

X

1

2

1

2

1

X

32

10

2

X

1

2

X

2

1

2

X

1

X

2

X

2

33

11

X

1

X

2

1

2

1

X

1

34

12

X

1

X

1

2

X

2

X

1

35

13

X

2

X

1

2

X

2

1

X

1

X

1

X

1

X

36

14

2

X

1

2

1

X

1

X

2

X

1

X

2

1

37

Hoja6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:AW18	Cell Value	="N.X"	text	YES

Thank you in advance

Regards,
Kishan

AlanY · May 15, 2023

try this in B5, copy down and across

=IF(B23<>"X","N.X.",IF(A23<>"X",1,A5+1))

Peter_SSs · May 15, 2023

Anything to tell us about conditional formatting this time?

Peter_SSs · May 15, 2023

Kishan said:
Please suggest a VBA solution.

Try ..

VBA Code:

Sub Count_X()
  With Range("B5").Resize(14, Rows("23:36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    .Value = .Offset(18).Value
    .SpecialCells(xlConstants, xlTextValues).ClearContents
    .SpecialCells(xlConstants, xlNumbers).Value = "N.X"
    .SpecialCells(xlBlanks).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

Kishan · May 15, 2023

AlanY said:
try this in B5, copy down and across

=IF(B23<>"X","N.X.",IF(A23<>"X",1,A5+1))

Hello AlanY, your formula gave me result as request. Thank you for your time and help.

Have a good start of the week.

Good Luck.

My Best Regards
Kishan

Kishan · May 15, 2023

Peter_SSs said:

Try ..

VBA Code:

Sub Count_X()
  With Range("B5").Resize(14, Rows("23:36").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column - 1)
    .Value = .Offset(18).Value
    .SpecialCells(xlConstants, xlTextValues).ClearContents
    .SpecialCells(xlConstants, xlNumbers).Value = "N.X"
    .SpecialCells(xlBlanks).FormulaR1C1 = "=N(RC[-1])*(COLUMN(RC)>2)+1"
    .Value = .Value
  End With
End Sub

Hello Peter_SSs, VBA work as request spot on. Thank you for your time and help.

Have a good start of the week.

Good Luck.

My Best Regards
Kishan

Peter_SSs · May 15, 2023

Cheers again.

Kishan · May 18, 2023

Hello Peter, I want the result as shown below where is “X” count them ans any number after or before “X” count them are in range B23:X36 and show the result in the range B5:X18. Please see the layout below.

NX.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

1

2

3

4

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

14

1

2

3

4

5

6

7

8

9

10

11

12

13

5

1

N.X

1

N.X

1

N.X

1

N.X

6

2

N.X

7

3

N.X

1

2

N.X

1

N.X

1

N.X

8

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

9

5

N.X

1

N.X

1

2

3

4

N.X

10

6

N.X

1

N.X

1

N.X

1

N.X

11

7

1

N.X

1

N.X

1

2

N.X

1

N.X

12

8

1

N.X

1

N.X

1

N.X

13

9

N.X

1

N.X

1

N.X

1

14

10

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

15

11

1

N.X

1

2

N.X

1

2

N.X

16

12

1

2

N.X

1

2

N.X

1

N.X

1

2

N.X

17

13

1

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

N.X

1

18

14

N.X

1

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

N.X

19

20

21

22

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

14

1

2

3

4

5

6

7

8

9

10

11

12

13

23

1

X

1

X

1

X

1

2

1

X

1

X

1

X

1

24

2

1

2

1

2

1

2

1

2

1

25

3

1

2

X

2

1

2

1

2

X

1

X

2

1

2

X

2

1

2

1

26

4

1

2

1

X

1

X

1

2

1

X

1

X

1

2

1

X

1

X

27

5

1

2

1

2

1

X

1

2

X

1

2

1

2

1

X

1

2

28

6

2

1

X

1

X

1

2

1

X

2

1

2

1

2

1

X

1

X

1

2

1

29

7

X

1

X

1

2

1

2

1

2

X

2

1

X

2

1

2

1

X

1

X

1

2

1

2

1

2

X

30

8

X

1

2

1

2

1

X

2

1

2

1

2

X

1

X

1

2

1

2

1

X

2

1

2

31

9

1

2

1

2

1

X

2

X

1

2

1

2

1

X

1

2

1

2

1

X

2

X

32

10

2

X

1

2

X

2

1

2

X

1

X

2

X

2

X

1

2

X

2

1

2

X

1

33

11

X

1

X

2

1

2

1

X

1

X

1

X

2

1

2

1

34

12

X

1

X

1

2

X

2

X

1

X

1

X

1

2

35

13

X

2

X

1

2

X

2

1

X

1

X

1

X

1

X

2

X

1

2

X

2

1

X

36

14

2

X

1

2

1

X

1

X

2

X

1

X

2

1

2

X

1

2

1

X

1

X

2

X

37

Hoja6

Cell Formulas
Range	Formula
U2:W2	U2	=IF(U23="","",IF(U23<>"X","N.X",IF(T23<>"X",1,T5+1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z5:AL18	Cell Value	="N.X"	text	YES
X5:X18	Cell Value	="N.X"	text	YES
B5:W18	Cell Value	="N.X"	text	YES

Does not permit so half massage is on the next sheet

Regards,
Kishan

Kishan · May 18, 2023

But now I am having small issue having data arranged as following way. When I run the macro I am getting the result as below. Is it possible can you fix the range B23:X36 of data result ws only with range B5:X18. So it should not affect the right onward columns.

NX.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

1

2

3

4

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

14

1

2

3

4

5

6

7

8

9

10

11

12

13

5

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

N.X

1

N.X

1

N.X

1

N.X

6

2

N.X

1

2

3

4

5

N.X

7

3

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

N.X

1

2

N.X

8

4

N.X

1

2

N.X

1

N.X

1

2

N.X

1

2

3

4

5

6

N.X

1

2

N.X

1

9

5

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

N.X

1

N.X

10

6

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

N.X

1

N.X

1

N.X

11

7

1

N.X

1

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

N.X

1

2

12

8

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

N.X

1

N.X

13

9

N.X

1

N.X

1

N.X

1

2

3

4

5

6

N.X

1

N.X

1

14

10

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

N.X

1

N.X

1

N.X

1

2

N.X

15

11

1

N.X

1

2

N.X

1

2

N.X

1

2

3

4

5

6

N.X

1

2

N.X

16

12

1

2

N.X

1

2

N.X

1

N.X

1

2

N.X

1

2

3

4

5

6

7

N.X

1

2

N.X

17

13

1

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

N.X

1

N.X

1

N.X

1

2

18

14

N.X

1

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

N.X

1

N.X

1

N.X

1

2

3

N.X

1

19

20

21

22

Serial Numbers

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

14

1

2

3

4

5

6

7

8

9

10

11

12

13

23

1

X

1

X

1

X

1

2

1

X

1

X

1

X

1

24

2

1

2

1

2

1

2

1

2

1

25

3

1

2

X

2

1

2

1

2

X

1

X

2

1

2

X

2

1

2

1

26

4

1

2

1

X

1

X

1

2

1

X

1

X

1

2

1

X

1

X

27

5

1

2

1

2

1

X

1

2

X

1

2

1

2

1

X

1

2

28

6

2

1

X

1

X

1

2

1

X

2

1

2

1

2

1

X

1

X

1

2

1

29

7

X

1

X

1

2

1

2

1

2

X

2

1

X

2

1

2

1

X

1

X

1

2

1

2

1

2

X

30

8

X

1

2

1

2

1

X

2

1

2

1

2

X

1

X

1

2

1

2

1

X

2

1

2

31

9

1

2

1

2

1

X

2

X

1

2

1

2

1

X

1

2

1

2

1

X

2

X

32

10

2

X

1

2

X

2

1

2

X

1

X

2

X

2

X

1

2

X

2

1

2

X

1

33

11

X

1

X

2

1

2

1

X

1

X

1

X

2

1

2

1

34

12

X

1

X

1

2

X

2

X

1

X

1

X

1

2

35

13

X

2

X

1

2

X

2

1

X

1

X

1

X

1

X

2

X

1

2

X

2

1

X

36

14

2

X

1

2

1

X

1

X

2

X

1

X

2

1

2

X

1

2

1

X

1

X

2

X

37

Hoja6

Cell Formulas
Range	Formula
U2:W2	U2	=IF(U23="","",IF(U23<>"X","N.X",IF(T23<>"X",1,T5+1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z5:AL18	Cell Value	="N.X"	text	YES
X5:X18	Cell Value	="N.X"	text	YES
B5:W18	Cell Value	="N.X"	text	YES

Regards,
Kishan

Peter_SSs · May 19, 2023

Sorry, I don't understand the problem or the resolution.

Count continuous “X’s” and covert the numbers into “N.X”

Kishan

Well-known Member

Excel Facts

AlanY

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Kishan

Well-known Member

Kishan

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Count continuous “X’s” and covert the numbers into “N.X”

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

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