Extract only 3 first sign "1", "X" Or "2" appear in the row

Kishan · Jun 24, 2018

Using Excel 2000

Hi,

Data In columns C:P, R:AE are the result columns.

I want VBA or Formula which can extract only 3 first sign "1", "X" Or "2" find in the each row

For example... there are 14 positions of 3 signs "1X2" in columns C:P
Search for 1st sign in the row 6 there is "1" in the C6 position P1, Extract Place "1" in the R6 Position P1.
Search for 2nd sign "X" is in the D6 Position P2, Extract Place "X" in the S6 Position P2.
Finally search for 3rd sign "2" is in the E6 Position P3, Extract Place "X" in the T6 Position P3.

Continue so on.... for all next rows

Note: colours are filled just to explain the example clearer.

Example data.

Book1

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

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

EM

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

6

1

X

2

1

X

2

X

1

X

2

7

1

X

1

X

2

X

1

X

1

X

2

8

1

X

1

2

1

X

1

X

1

2

1

X

2

9

X

1

X

1

X

1

X

1

X

1

X

1

10

X

1

X

1

X

2

1

2

X

1

2

11

2

X

1

2

X

1

2

1

2

X

1

12

X

1

X

1

X

1

X

1

13

1

X

1

2

1

2

1

2

X

2

X

1

X

2

14

X

2

X

1

X

1

X

1

X

2

1

15

1

X

1

2

X

1

X

2

16

1

2

1

2

X

1

2

X

2

1

X

1

2

X

17

1

X

1

2

X

1

X

1

X

1

X

2

18

1

X

1

2

1

2

1

X

1

X

2

19

1

X

1

X

1

X

20

X

1

2

X

1

2

X

1

2

X

1

2

21

1

X

1

X

1

X

1

X

22

1

X

2

1

2

1

X

2

23

1

2

X

1

2

X

24

1

2

1

X

1

2

X

25

1

X

1

2

1

X

2

26

X

1

X

1

2

X

1

X

1

2

27

X

2

1

2

1

X

1

X

2

1

28

1

X

2

1

2

X

1

X

1

X

2

29

2

1

X

1

X

1

X

1

2

1

X

30

1

X

1

2

1

2

1

X

1

X

2

31

1

X

1

2

1

X

1

X

1

X

1

X

2

32

2

1

X

1

X

1

X

1

2

1

X

33

X

1

2

1

2

X

1

X

1

2

34

1

2

1

2

1

2

1

2

1

2

35

X

1

X

1

2

1

X

1

X

1

2

36

2

X

1

X

1

X

1

X

1

2

X

1

37

1

X

2

X

1

X

1

2

1

X

1

X

2

38

X

1

X

1

X

1

X

1

2

1

X

1

2

39

1

X

1

2

X

1

X

1

X

1

2

1

X

2

40

1

X

1

X

1

X

2

1

X

1

X

2

41

1

2

1

X

1

X

1

X

2

1

2

X

42

X

1

2

1

2

1

X

1

2

43

2

X

1

X

1

X

1

X

2

X

1

44

X

2

1

X

1

2

1

2

1

X

2

1

45

1

X

1

X

1

X

1

X

46

X

1

X

2

1

X

1

2

47

1

X

1

X

1

X

1

X

48

X

1

X

1

X

1

49

1

X

1

2

1

X

1

X

2

50

1

X

1

X

2

X

1

X

2

51

1

2

1

X

1

2

X

52

2

X

1

X

1

X

1

2

X

1

53

1

X

1

2

1

2

1

2

1

X

2

54

1

X

1

X

1

X

1

X

55

X

1

X

1

2

1

2

X

1

X

1

2

56

1

X

1

X

1

X

2

1

X

2

57

1

2

1

X

2

1

X

1

X

1

2

X

58

1

X

1

2

1

X

1

X

1

X

2

59

1

2

1

X

1

X

1

X

1

2

X

60

1

2

1

X

1

2

1

X

1

X

1

2

X

61

2

1

X

1

X

1

X

2

X

2

1

X

62

1

X

1

X

1

2

1

2

1

X

2

63

1

2

1

2

1

2

X

1

2

X

64

1

X

1

X

65

1

2

1

X

1

X

1

2

X

66

67

Sheet1

Thank you in advance

Regards,
Kishan

Oscar Cronquist · Jun 24, 2018

	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
1
2
3
4
5	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
6	1	X	2	2	2	1	X	X	2	2	X	X	1	1		1	X	2
7	1	X	1	1	X	X	2	2	X	X	1	1	X	X		1	X					2
8	1	X	1	2	2	1	X	1	1	X	1	2	2	1		1	X		2
9	X	1	X	1	X	1	1	X	1	1	1	X	X	1		X	1
10	X	X	X	1	1	1	1	X	1	X	2	1	2	X		X			1							2
11	2	X	1	1	1	2	X	X	X	1	1	1	2	1		2	X	1
12	X	1	X	X	1	1	1	1	1	1	1	X	1	1		X	1
13	1	X	X	1	2	1	2	1	1	1	2	X	2	X		1	X			2
14	X	2	X	1	1	X	1	1	1	1	X	1	1	X		X	2		1
15	1	1	1	1	1	1	X	X	X	1	1	1	2	X		1						X						2
16	1	2	1	2	X	1	1	2	X	2	1	1	1	X		1	2			X
17	1	X	X	1	1	2	X	X	1	X	1	1	X	1		1	X				2
18	1	X	X	1	1	2	1	1	2	2	1	X	1	1		1	X				2
19	1	1	1	X	1	1	1	1	X	1	1	1	1	1		1			X
20	X	1	2	X	1	1	1	2	X	1	1	2	X	X		X	1	2
21	1	1	1	X	1	X	X	1	X	1	1	1	1	1		1			X
22	1	1	1	1	1	X	X	2	1	1	1	1	1	2		1					X		2
23	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1										2	X
24	1	2	2	1	1	1	1	1	1	1	1	X	1	1		1	2										X
25	1	1	X	1	1	1	1	1	1	1	1	1	2	1		1		X										2
26	X	X	X	1	1	1	X	1	1	2	X	1	1	1		X			1						2
27	X	2	1	1	2	1	1	1	1	1	X	X	1	X		X	2	1
28	1	X	2	2	1	1	1	2	X	X	1	1	1	X		1	X	2
29	2	1	X	X	1	1	X	X	1	X	1	1	1	1		2	1	X
30	1	X	1	2	2	1	1	1	1	2	1	1	1	X		1	X		2
31	1	1	X	1	2	2	2	1	1	X	1	X	1	X		1		X		2
32	2	1	X	1	X	1	X	1	1	1	1	1	1	1		2	1	X
33	X	1	1	1	1	1	2	1	2	X	X	1	X	X		X	1					2
34	1	2	1	1	2	1	1	1	2	2	2	1	2	2		1	2
35	X	1	1	1	1	X	1	2	1	1	X	1	X	X		X	1						2
36	2	X	1	1	1	X	X	1	1	X	1	1	X	1		2	X	1
37	1	X	2	X	1	X	1	1	1	1	1	2	1	X		1	X	2
38	X	1	X	X	1	X	1	1	X	1	2	2	1	1		X	1									2
39	1	X	X	1	2	X	1	1	X	1	X	1	2	1		1	X			2
40	1	X	1	1	X	1	X	X	2	1	1	1	1	X		1	X							2
41	1	2	1	1	1	1	X	1	X	X	1	X	2	2		1	2					X
42	X	1	1	1	1	1	1	1	2	1	2	2	1	X		X	1							2
43	2	X	X	1	1	X	1	1	X	X	X	X	1	X		2	X		1
44	X	2	1	1	X	1	1	2	1	1	1	1	2	1		X	2	1
45	1	X	X	1	1	1	1	1	1	1	X	1	1	X		1	X
46	X	X	1	1	1	X	X	2	1	1	1	1	X	X		X		1					2
47	1	1	1	X	1	X	1	1	1	1	X	1	1	1		1			X
48	X	X	1	1	X	1	1	1	1	1	1	1	1	1		X		1
49	1	1	1	X	1	1	1	1	2	1	1	X	1	1		1			X					2
50	1	1	1	1	1	X	X	1	X	2	X	X	X	X		1					X				2
51	1	1	1	1	2	1	X	X	X	1	1	1	1	1		1				2		X
52	2	X	1	1	X	1	1	X	X	1	1	1	1	2		2	X	1
53	1	X	X	1	1	1	1	1	1	2	1	2	1	2		1	X								2
54	1	1	1	1	X	1	1	X	X	X	1	1	X	1		1				X
55	X	1	1	1	X	1	2	1	1	1	2	X	1	1		X	1					2
56	1	1	X	X	1	X	1	1	X	X	X	2	1	1		1		X									2
57	1	2	2	1	1	1	X	2	1	X	X	X	1	X		1	2					X
58	1	1	X	1	1	2	1	1	1	1	X	1	X	1		1		X			2
59	1	2	2	1	1	X	1	1	1	1	1	X	1	X		1	2				X
60	1	1	1	1	2	1	X	1	2	2	1	X	1	X		1				2		X
61	2	1	1	X	1	1	1	X	1	1	X	X	2	X		2	1		X
62	1	1	1	1	X	1	1	X	1	1	2	1	1	2		1				X						2
63	1	1	1	1	1	1	2	1	2	1	1	1	2	X		1						2							X
64	1	1	1	1	1	1	1	1	1	1	1	1	1	X		1													X
65	1	2	2	1	1	1	1	1	1	X	X	1	1	X		1	2								X

Sheet: Sheet27

Formula in cell R6:
=IF(MATCH(C6,$C6:$P6,0)=COLUMNS($A$1:A1),C6,"")

Rick Rothstein · Jun 24, 2018

Here is a macro that should work...

Code:

Sub First1X2s()
  Dim Rw As Long
  On Error Resume Next
  Application.ScreenUpdating = False
  For Rw = 6 To Cells(Rows.Count, "C").End(xlUp).Row
    Rows(Rw).Find(1, , , xlWhole, , , , , False).Offset(, 15) = 1
    Rows(Rw).Find("X", , , xlWhole, , , , , False).Offset(, 15) = "X"
    Rows(Rw).Find(2, , , xlWhole, , , , , False).Offset(, 15) = 2
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

Kishan · Jun 24, 2018

Oscar Cronquist said:
Sheet: Sheet27

<tbody>
</tbody>

Formula in cell R6:
=IF(MATCH(C6,$C6:$P6,0)=COLUMNS($A$1:A1),C6,"")

Hi Oscar Cronquist, after applying your formula I realized that in the row 62 cell AB62 my results is not correct

Your formula worked spot on

Thank you very much for your help and time

Have a nice weekend

Regards,
Kishan

Kishan · Jun 24, 2018

Rick Rothstein said:

Here is a macro that should work...

Code:

Sub First1X2s()
   Dim Rw As Long
   On Error Resume Next
  Application.ScreenUpdating = False
   For Rw = 6 To Cells(Rows.Count, "C").End(xlUp).Row
     Rows(Rw).Find(1, , , xlWhole, , , , , False).Offset(, 15) = 1
     Rows(Rw).Find("X", , , xlWhole, , , , , False).Offset(, 15) = "X"
     Rows(Rw).Find(2, , , xlWhole, , , , , False).Offset(, 15) = 2
   Next
   On Error GoTo 0
   Application.ScreenUpdating = True
 End Sub

Hi Rick, result side blank, I don't get any results,

Please could you check?

Thank you

Regards,
Kishan

Kishan · Jun 24, 2018

Kishan said:
Hi Rick, result side blank, I don't get any results,

Please could you check?

Thank you

Regards,
Kishan

Hi Rick, it worked Perfect!!

Just changed the following

This.....
Rows(Rw).Find(1, , , xlWhole, , , , , False).Offset(, 15) = 1
Rows(Rw).Find("X", , , xlWhole, , , , , False).Offset(, 15) = "X"
Rows(Rw).Find(2, , , xlWhole, , , , , False).Offset(, 15) = 2

To this....Insted 5 Comas, only 4 comas.
Rows(Rw).Find(1, , , xlWhole, , , , False).Offset(, 15) = 1
Rows(Rw).Find("X", , , xlWhole, , , , False).Offset(, 15) = "X"
Rows(Rw).Find(2, , , xlWhole, , , , False).Offset(, 15) = 2

Thank you very much for your help and time

Have a nice weekend

Regards,
Kishan

Kishan · Jun 30, 2018

Kishan said:
Hi Rick, it worked Perfect!!

Just changed the following

This.....
Rows(Rw).Find(1, , , xlWhole, , , , , False).Offset(, 15) = 1
Rows(Rw).Find("X", , , xlWhole, , , , , False).Offset(, 15) = "X"
Rows(Rw).Find(2, , , xlWhole, , , , , False).Offset(, 15) = 2

To this....Insted 5 Comas, only 4 comas.
Rows(Rw).Find(1, , , xlWhole, , , , False).Offset(, 15) = 1
Rows(Rw).Find("X", , , xlWhole, , , , False).Offset(, 15) = "X"
Rows(Rw).Find(2, , , xlWhole, , , , False).Offset(, 15) = 2

Thank you very much for your help and time

Have a nice weekend

Regards,
Kishan

Hi Rick, may I ask you a modification #post1 "regarding the range", please can you set the search range fixed columns C:P, instead "searching data in the xlWhole Row"

Please need your help

Thank you

Regards,
Kishan

Kishan · Jun 30, 2018

Kishan said:
Hi Rick, may I ask you a modification #post1 "regarding the range", please can you set the search range fixed columns C:P, instead "searching data in the xlWhole Row"

Please need your help

Thank you

Regards,
Kishan

Hi Rick, finally I could be able to modified the code with hard work many hours changing the lines with different parameters

Here is the modified code

Code:

Sub Extract_First1X2s()
  
    Sheets("Results").Select
    Range("W6:AJ5006").Select
    Selection.ClearContents
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.ColorIndex = 1
    Range("W6").Select
  
  
  Dim Rw As Long
  On Error Resume Next
  Application.ScreenUpdating = False
  For Rw = 6 To Cells(Rows.Count, "B").End(xlUp).row
    Range("B" & Rw, "P" & Rw).Find(1, , , xlWhole, , , , False).Offset(, 15) = 1
    Range("B" & Rw, "P" & Rw).Find(1, , , xlWhole, , , , False).Offset(, 15).Interior.ColorIndex = 10
    Range("B" & Rw, "P" & Rw).Find(1, , , xlWhole, , , , False).Offset(, 15).Font.ColorIndex = 2
    
    Range("B" & Rw, "P" & Rw).Find("X", , , xlWhole, , , , False).Offset(, 15) = "X"
    Range("B" & Rw, "P" & Rw).Find("X", , , xlWhole, , , , False).Offset(, 15).Interior.ColorIndex = 5
    Range("B" & Rw, "P" & Rw).Find("X", , , xlWhole, , , , False).Offset(, 15).Font.ColorIndex = 2
    
    Range("B" & Rw, "P" & Rw).Find(2, , , xlWhole, , , , False).Offset(, 15) = 2
    Range("B" & Rw, "P" & Rw).Find(2, , , xlWhole, , , , False).Offset(, 15).Interior.ColorIndex = 3
    Range("B" & Rw, "P" & Rw).Find(2, , , xlWhole, , , , False).Offset(, 15).Font.ColorIndex = 2
  
  Next
  On Error GoTo 0
  Application.ScreenUpdating = True
End Sub

Thank you

Regards,
Kishan

Extract only 3 first sign "1", "X" Or "2" appear in the row

Kishan

Well-known Member

Excel Facts

Oscar Cronquist

Board Regular

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

	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
1
2
3
4
5	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
6	1	X	2	2	2	1	X	X	2	2	X	X	1	1		1	X	2
7	1	X	1	1	X	X	2	2	X	X	1	1	X	X		1	X					2
8	1	X	1	2	2	1	X	1	1	X	1	2	2	1		1	X		2
9	X	1	X	1	X	1	1	X	1	1	1	X	X	1		X	1
10	X	X	X	1	1	1	1	X	1	X	2	1	2	X		X			1							2
11	2	X	1	1	1	2	X	X	X	1	1	1	2	1		2	X	1
12	X	1	X	X	1	1	1	1	1	1	1	X	1	1		X	1
13	1	X	X	1	2	1	2	1	1	1	2	X	2	X		1	X			2
14	X	2	X	1	1	X	1	1	1	1	X	1	1	X		X	2		1
15	1	1	1	1	1	1	X	X	X	1	1	1	2	X		1						X						2
16	1	2	1	2	X	1	1	2	X	2	1	1	1	X		1	2			X
17	1	X	X	1	1	2	X	X	1	X	1	1	X	1		1	X				2
18	1	X	X	1	1	2	1	1	2	2	1	X	1	1		1	X				2
19	1	1	1	X	1	1	1	1	X	1	1	1	1	1		1			X
20	X	1	2	X	1	1	1	2	X	1	1	2	X	X		X	1	2
21	1	1	1	X	1	X	X	1	X	1	1	1	1	1		1			X
22	1	1	1	1	1	X	X	2	1	1	1	1	1	2		1					X		2
23	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1										2	X
24	1	2	2	1	1	1	1	1	1	1	1	X	1	1		1	2										X
25	1	1	X	1	1	1	1	1	1	1	1	1	2	1		1		X										2
26	X	X	X	1	1	1	X	1	1	2	X	1	1	1		X			1						2
27	X	2	1	1	2	1	1	1	1	1	X	X	1	X		X	2	1
28	1	X	2	2	1	1	1	2	X	X	1	1	1	X		1	X	2
29	2	1	X	X	1	1	X	X	1	X	1	1	1	1		2	1	X
30	1	X	1	2	2	1	1	1	1	2	1	1	1	X		1	X		2
31	1	1	X	1	2	2	2	1	1	X	1	X	1	X		1		X		2
32	2	1	X	1	X	1	X	1	1	1	1	1	1	1		2	1	X
33	X	1	1	1	1	1	2	1	2	X	X	1	X	X		X	1					2
34	1	2	1	1	2	1	1	1	2	2	2	1	2	2		1	2
35	X	1	1	1	1	X	1	2	1	1	X	1	X	X		X	1						2
36	2	X	1	1	1	X	X	1	1	X	1	1	X	1		2	X	1
37	1	X	2	X	1	X	1	1	1	1	1	2	1	X		1	X	2
38	X	1	X	X	1	X	1	1	X	1	2	2	1	1		X	1									2
39	1	X	X	1	2	X	1	1	X	1	X	1	2	1		1	X			2
40	1	X	1	1	X	1	X	X	2	1	1	1	1	X		1	X							2
41	1	2	1	1	1	1	X	1	X	X	1	X	2	2		1	2					X
42	X	1	1	1	1	1	1	1	2	1	2	2	1	X		X	1							2
43	2	X	X	1	1	X	1	1	X	X	X	X	1	X		2	X		1
44	X	2	1	1	X	1	1	2	1	1	1	1	2	1		X	2	1
45	1	X	X	1	1	1	1	1	1	1	X	1	1	X		1	X
46	X	X	1	1	1	X	X	2	1	1	1	1	X	X		X		1					2
47	1	1	1	X	1	X	1	1	1	1	X	1	1	1		1			X
48	X	X	1	1	X	1	1	1	1	1	1	1	1	1		X		1
49	1	1	1	X	1	1	1	1	2	1	1	X	1	1		1			X					2
50	1	1	1	1	1	X	X	1	X	2	X	X	X	X		1					X				2
51	1	1	1	1	2	1	X	X	X	1	1	1	1	1		1				2		X
52	2	X	1	1	X	1	1	X	X	1	1	1	1	2		2	X	1
53	1	X	X	1	1	1	1	1	1	2	1	2	1	2		1	X								2
54	1	1	1	1	X	1	1	X	X	X	1	1	X	1		1				X
55	X	1	1	1	X	1	2	1	1	1	2	X	1	1		X	1					2
56	1	1	X	X	1	X	1	1	X	X	X	2	1	1		1		X									2
57	1	2	2	1	1	1	X	2	1	X	X	X	1	X		1	2					X
58	1	1	X	1	1	2	1	1	1	1	X	1	X	1		1		X			2
59	1	2	2	1	1	X	1	1	1	1	1	X	1	X		1	2				X
60	1	1	1	1	2	1	X	1	2	2	1	X	1	X		1				2		X
61	2	1	1	X	1	1	1	X	1	1	X	X	2	X		2	1		X
62	1	1	1	1	X	1	1	X	1	1	2	1	1	2		1				X						2
63	1	1	1	1	1	1	2	1	2	1	1	1	2	X		1						2							X
64	1	1	1	1	1	1	1	1	1	1	1	1	1	X		1													X
65	1	2	2	1	1	1	1	1	1	X	X	1	1	X		1	2								X

	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
1
2
3
4
5	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
6	1	X	2	2	2	1	X	X	2	2	X	X	1	1		1	X	2
7	1	X	1	1	X	X	2	2	X	X	1	1	X	X		1	X					2
8	1	X	1	2	2	1	X	1	1	X	1	2	2	1		1	X		2
9	X	1	X	1	X	1	1	X	1	1	1	X	X	1		X	1
10	X	X	X	1	1	1	1	X	1	X	2	1	2	X		X			1							2
11	2	X	1	1	1	2	X	X	X	1	1	1	2	1		2	X	1
12	X	1	X	X	1	1	1	1	1	1	1	X	1	1		X	1
13	1	X	X	1	2	1	2	1	1	1	2	X	2	X		1	X			2
14	X	2	X	1	1	X	1	1	1	1	X	1	1	X		X	2		1
15	1	1	1	1	1	1	X	X	X	1	1	1	2	X		1						X						2
16	1	2	1	2	X	1	1	2	X	2	1	1	1	X		1	2			X
17	1	X	X	1	1	2	X	X	1	X	1	1	X	1		1	X				2
18	1	X	X	1	1	2	1	1	2	2	1	X	1	1		1	X				2
19	1	1	1	X	1	1	1	1	X	1	1	1	1	1		1			X
20	X	1	2	X	1	1	1	2	X	1	1	2	X	X		X	1	2
21	1	1	1	X	1	X	X	1	X	1	1	1	1	1		1			X
22	1	1	1	1	1	X	X	2	1	1	1	1	1	2		1					X		2
23	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1										2	X
24	1	2	2	1	1	1	1	1	1	1	1	X	1	1		1	2										X
25	1	1	X	1	1	1	1	1	1	1	1	1	2	1		1		X										2
26	X	X	X	1	1	1	X	1	1	2	X	1	1	1		X			1						2
27	X	2	1	1	2	1	1	1	1	1	X	X	1	X		X	2	1
28	1	X	2	2	1	1	1	2	X	X	1	1	1	X		1	X	2
29	2	1	X	X	1	1	X	X	1	X	1	1	1	1		2	1	X
30	1	X	1	2	2	1	1	1	1	2	1	1	1	X		1	X		2
31	1	1	X	1	2	2	2	1	1	X	1	X	1	X		1		X		2
32	2	1	X	1	X	1	X	1	1	1	1	1	1	1		2	1	X
33	X	1	1	1	1	1	2	1	2	X	X	1	X	X		X	1					2
34	1	2	1	1	2	1	1	1	2	2	2	1	2	2		1	2
35	X	1	1	1	1	X	1	2	1	1	X	1	X	X		X	1						2
36	2	X	1	1	1	X	X	1	1	X	1	1	X	1		2	X	1
37	1	X	2	X	1	X	1	1	1	1	1	2	1	X		1	X	2
38	X	1	X	X	1	X	1	1	X	1	2	2	1	1		X	1									2
39	1	X	X	1	2	X	1	1	X	1	X	1	2	1		1	X			2
40	1	X	1	1	X	1	X	X	2	1	1	1	1	X		1	X							2
41	1	2	1	1	1	1	X	1	X	X	1	X	2	2		1	2					X
42	X	1	1	1	1	1	1	1	2	1	2	2	1	X		X	1							2
43	2	X	X	1	1	X	1	1	X	X	X	X	1	X		2	X		1
44	X	2	1	1	X	1	1	2	1	1	1	1	2	1		X	2	1
45	1	X	X	1	1	1	1	1	1	1	X	1	1	X		1	X
46	X	X	1	1	1	X	X	2	1	1	1	1	X	X		X		1					2
47	1	1	1	X	1	X	1	1	1	1	X	1	1	1		1			X
48	X	X	1	1	X	1	1	1	1	1	1	1	1	1		X		1
49	1	1	1	X	1	1	1	1	2	1	1	X	1	1		1			X					2
50	1	1	1	1	1	X	X	1	X	2	X	X	X	X		1					X				2
51	1	1	1	1	2	1	X	X	X	1	1	1	1	1		1				2		X
52	2	X	1	1	X	1	1	X	X	1	1	1	1	2		2	X	1
53	1	X	X	1	1	1	1	1	1	2	1	2	1	2		1	X								2
54	1	1	1	1	X	1	1	X	X	X	1	1	X	1		1				X
55	X	1	1	1	X	1	2	1	1	1	2	X	1	1		X	1					2
56	1	1	X	X	1	X	1	1	X	X	X	2	1	1		1		X									2
57	1	2	2	1	1	1	X	2	1	X	X	X	1	X		1	2					X
58	1	1	X	1	1	2	1	1	1	1	X	1	X	1		1		X			2
59	1	2	2	1	1	X	1	1	1	1	1	X	1	X		1	2				X
60	1	1	1	1	2	1	X	1	2	2	1	X	1	X		1				2		X
61	2	1	1	X	1	1	1	X	1	1	X	X	2	X		2	1		X
62	1	1	1	1	X	1	1	X	1	1	2	1	1	2		1				X						2
63	1	1	1	1	1	1	2	1	2	1	1	1	2	X		1						2							X
64	1	1	1	1	1	1	1	1	1	1	1	1	1	X		1													X
65	1	2	2	1	1	1	1	1	1	X	X	1	1	X		1	2								X

Extract only 3 first sign "1", "X" Or "2" appear in the row

Well-known Member

Excel Facts

Board Regular

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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

	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
1
2
3
4
5	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14	EM	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
6	1	X	2	2	2	1	X	X	2	2	X	X	1	1		1	X	2
7	1	X	1	1	X	X	2	2	X	X	1	1	X	X		1	X					2
8	1	X	1	2	2	1	X	1	1	X	1	2	2	1		1	X		2
9	X	1	X	1	X	1	1	X	1	1	1	X	X	1		X	1
10	X	X	X	1	1	1	1	X	1	X	2	1	2	X		X			1							2
11	2	X	1	1	1	2	X	X	X	1	1	1	2	1		2	X	1
12	X	1	X	X	1	1	1	1	1	1	1	X	1	1		X	1
13	1	X	X	1	2	1	2	1	1	1	2	X	2	X		1	X			2
14	X	2	X	1	1	X	1	1	1	1	X	1	1	X		X	2		1
15	1	1	1	1	1	1	X	X	X	1	1	1	2	X		1						X						2
16	1	2	1	2	X	1	1	2	X	2	1	1	1	X		1	2			X
17	1	X	X	1	1	2	X	X	1	X	1	1	X	1		1	X				2
18	1	X	X	1	1	2	1	1	2	2	1	X	1	1		1	X				2
19	1	1	1	X	1	1	1	1	X	1	1	1	1	1		1			X
20	X	1	2	X	1	1	1	2	X	1	1	2	X	X		X	1	2
21	1	1	1	X	1	X	X	1	X	1	1	1	1	1		1			X
22	1	1	1	1	1	X	X	2	1	1	1	1	1	2		1					X		2
23	1	1	1	1	1	1	1	1	1	1	2	X	X	1		1										2	X
24	1	2	2	1	1	1	1	1	1	1	1	X	1	1		1	2										X
25	1	1	X	1	1	1	1	1	1	1	1	1	2	1		1		X										2
26	X	X	X	1	1	1	X	1	1	2	X	1	1	1		X			1						2
27	X	2	1	1	2	1	1	1	1	1	X	X	1	X		X	2	1
28	1	X	2	2	1	1	1	2	X	X	1	1	1	X		1	X	2
29	2	1	X	X	1	1	X	X	1	X	1	1	1	1		2	1	X
30	1	X	1	2	2	1	1	1	1	2	1	1	1	X		1	X		2
31	1	1	X	1	2	2	2	1	1	X	1	X	1	X		1		X		2
32	2	1	X	1	X	1	X	1	1	1	1	1	1	1		2	1	X
33	X	1	1	1	1	1	2	1	2	X	X	1	X	X		X	1					2
34	1	2	1	1	2	1	1	1	2	2	2	1	2	2		1	2
35	X	1	1	1	1	X	1	2	1	1	X	1	X	X		X	1						2
36	2	X	1	1	1	X	X	1	1	X	1	1	X	1		2	X	1
37	1	X	2	X	1	X	1	1	1	1	1	2	1	X		1	X	2
38	X	1	X	X	1	X	1	1	X	1	2	2	1	1		X	1									2
39	1	X	X	1	2	X	1	1	X	1	X	1	2	1		1	X			2
40	1	X	1	1	X	1	X	X	2	1	1	1	1	X		1	X							2
41	1	2	1	1	1	1	X	1	X	X	1	X	2	2		1	2					X
42	X	1	1	1	1	1	1	1	2	1	2	2	1	X		X	1							2
43	2	X	X	1	1	X	1	1	X	X	X	X	1	X		2	X		1
44	X	2	1	1	X	1	1	2	1	1	1	1	2	1		X	2	1
45	1	X	X	1	1	1	1	1	1	1	X	1	1	X		1	X
46	X	X	1	1	1	X	X	2	1	1	1	1	X	X		X		1					2
47	1	1	1	X	1	X	1	1	1	1	X	1	1	1		1			X
48	X	X	1	1	X	1	1	1	1	1	1	1	1	1		X		1
49	1	1	1	X	1	1	1	1	2	1	1	X	1	1		1			X					2
50	1	1	1	1	1	X	X	1	X	2	X	X	X	X		1					X				2
51	1	1	1	1	2	1	X	X	X	1	1	1	1	1		1				2		X
52	2	X	1	1	X	1	1	X	X	1	1	1	1	2		2	X	1
53	1	X	X	1	1	1	1	1	1	2	1	2	1	2		1	X								2
54	1	1	1	1	X	1	1	X	X	X	1	1	X	1		1				X
55	X	1	1	1	X	1	2	1	1	1	2	X	1	1		X	1					2
56	1	1	X	X	1	X	1	1	X	X	X	2	1	1		1		X									2
57	1	2	2	1	1	1	X	2	1	X	X	X	1	X		1	2					X
58	1	1	X	1	1	2	1	1	1	1	X	1	X	1		1		X			2
59	1	2	2	1	1	X	1	1	1	1	1	X	1	X		1	2				X
60	1	1	1	1	2	1	X	1	2	2	1	X	1	X		1				2		X
61	2	1	1	X	1	1	1	X	1	1	X	X	2	X		2	1		X
62	1	1	1	1	X	1	1	X	1	1	2	1	1	2		1				X						2
63	1	1	1	1	1	1	2	1	2	1	1	1	2	X		1						2							X
64	1	1	1	1	1	1	1	1	1	1	1	1	1	X		1													X
65	1	2	2	1	1	1	1	1	1	X	X	1	1	X		1	2								X