Count 1-X-2 Before "X" 11th Pos

Kishan · Sep 12, 2015

Hi,

I need to count in the each row 1, X & 2 before the X is finding in the 11th position (and stop counting if breaks with other sign is found). Example is attached

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

Year

Date

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

Empty

Before "X" 11th Position

2

Year

Date

P1

P2

P3

P4

P5

P6

P7

P8

P9

P10

P11

P12

P13

P14

Empty

Count 1

Count X

Count 2

3

2013

01/12/2013

2

X

2

1

X

1

2

X

1

4

2013

02/12/2013

2

X

1

2

1

X

2

1

2

1

2

X

5

2013

03/12/2013

1

X

1

X

1

X

1

2

6

2013

04/12/2013

1

2

1

2

X

1

X

1

7

2013

05/12/2013

1

X

1

X

1

2

X

1

X

8

2013

06/12/2013

1

2

X

1

X

1

X

9

2013

07/12/2013

1

X

1

2

1

X

1

X

1

10

2013

08/12/2013

1

2

1

2

1

X

1

2

11

2013

09/12/2013

2

X

2

1

2

X

2

1

X

1

2

12

2013

10/12/2013

1

X

1

2

1

2

1

X

1

13

2013

11/12/2013

1

2

1

X

1

X

1

X

1

14

2013

12/12/2013

1

2

X

2

1

2

X

1

X

2

1

15

2013

13/12/2013

1

X

1

X

1

2

1

2

16

2013

14/12/2013

X

1

2

1

2

X

1

3

17

2013

15/12/2013

2

X

1

2

X

2

X

1

18

2013

16/12/2013

X

1

X

1

X

1

2

3

19

2013

17/12/2013

2

X

1

2

1

2

1

2

1

X

1

X

1

20

2013

18/12/2013

X

1

2

1

2

1

X

2

X

2

1

21

2013

19/12/2013

1

2

1

2

X

2

X

1

2

1

22

2013

20/12/2013

2

1

X

2

1

X

1

2

Count 1-X-2 Before "X" 11th Pos

Need VBA solution if possible

Please help!

Thanks

Regards,
Kishan

onlyadrafter · Sep 12, 2015

Hello,

You haven't specified what you want to do with the results. Also, your question is a bit confusing, what results do you expect from your example?

Kishan · Sep 12, 2015

onlyadrafter said:
Hello,

You haven't specified what you want to do with the results. Also, your question is a bit confusing, what results do you expect from your example?

Hi onlyadrafter,

My expected results are shown in the column R, S & T in the cells R3:T22, Please Select all copy and paste in the Excel you will get the answer I think in the web it is visible only up to column O I do know why?

I am using excel 2000

Regards,
Moti

Kishan · Sep 12, 2015

onlyadrafter said:
Hello,

You haven't specified what you want to do with the results. Also, your question is a bit confusing,

Hi onlyadrafter, sorry noticed did not reply your confusion about question.

Regarding question I want to count “X” which find in column M and it has 11^th position within result range is shown cells C3:P22

For example:
Row A5:T5 The result is shown in Cells C5:P5 and the “X” is in the cell M5 is in the 11^th position counting C5 as 1^st position “cell M5 will be the 11^th position” so I want to count how many times 1, X or 2 appear before “X” is find in column M, hope this makes clear

Regards,
Moti

onlyadrafter · Sep 12, 2015

Hello

This gives the answers as per your example

Code:

Sub COUNT_1_2_X()
    For MY_ROWS = 3 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("M" & MY_ROWS).Value = "X" Then
            MY_VALUE = Cells(MY_ROWS, 12)
            MY_COUNT = 1
            For MY_COLS = 11 To 3 Step -1
                If Cells(MY_ROWS, MY_COLS).Value = MY_VALUE Then
                    MY_COUNT = MY_COUNT + 1
                Else
                    GoTo CONT
                End If
            Next MY_COLS
CONT:
            Select Case MY_VALUE
                Case 1
                    Cells(MY_ROWS, 18).Value = MY_COUNT
                Case "X"
                    Cells(MY_ROWS, 19).Value = MY_COUNT
                Case 2
                    Cells(MY_ROWS, 20).Value = MY_COUNT
            End Select
            MY_COUNT = 0
        End If
    Next MY_ROWS
End Sub

MickG · Sep 12, 2015

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG12Sep03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("M3"), Range("M" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] Range("R3").Resize(Rng.Count, 3)
    .Interior.ColorIndex = xlNone
    .Font.ColorIndex = 2
    .HorizontalAlignment = xlCenter
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 1
    [COLOR="Navy"]If[/COLOR] Dn.Value = "X" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Temp = Dn.Offset(, -1)
        [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] Temp.Value = Dn.Offset(, -c)
            c = c + 1
        [COLOR="Navy"]Loop[/COLOR]
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Temp
        [COLOR="Navy"]Case[/COLOR] "1"
            [COLOR="Navy"]With[/COLOR] Dn.Offset(, 5)
                .Value = c - 1
                .Interior.ColorIndex = Temp.Interior.ColorIndex
            [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]Case[/COLOR] "X"
            [COLOR="Navy"]With[/COLOR] Dn.Offset(, 6)
                .Value = c - 1
                .Interior.ColorIndex = Temp.Interior.ColorIndex
            [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]Case[/COLOR] "2"
            [COLOR="Navy"]With[/COLOR] Dn.Offset(, 7)
                .Value = c - 1
                .Interior.ColorIndex = Temp.Interior.ColorIndex
            [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Kishan · Sep 12, 2015

onlyadrafter said:
Hello This gives the answers as per your example

MickG said:
Try this:-

Thank you so much onlyadrafter and MickG for giving a key solution as I request it is working like magic

MickG your solution is smarter it colours the same format, which is, find in the result row WOW!

If it is not much to bother May I ask you is it possible VBA can give me an option to input the “X” position
From 2nd to 14th position Just my thought.

Regards,
Kishan

MickG · Sep 12, 2015

Can you show an example ????

onlyadrafter · Sep 12, 2015

Hello,

does this work as expected?

Code:

Sub COUNT_COLOUR_1_2_X()
    Columns("C:T").Interior.ColorIndex = xlNone
    MY_POSITION = InputBox("Plase enter X Position", "X POSITION") + 2
    For MY_ROWS = 3 To Range("A" & Rows.Count).End(xlUp).Row
        If Cells(MY_ROWS, MY_POSITION).Value = "X" Then
            Cells(MY_ROWS, MY_POSITION).Interior.ColorIndex = 6
            MY_VALUE = Cells(MY_ROWS, MY_POSITION - 1)
            MY_COUNT = 1
            For MY_COLS = MY_POSITION - 1 To 3 Step -1
                If Cells(MY_ROWS, MY_COLS).Value = MY_VALUE Then
                    Select Case MY_VALUE
                        Case 1
                            Cells(MY_ROWS, MY_COLS).Interior.ColorIndex = 3 'red
                        Case "X"
                            Cells(MY_ROWS, MY_COLS).Interior.ColorIndex = 5 'blue
                        Case 2
                            Cells(MY_ROWS, MY_COLS).Interior.ColorIndex = 7
                End Select
                    MY_COUNT = MY_COUNT + 1
                Else
                    GoTo CONT
                End If
            Next MY_COLS
CONT:
            Select Case MY_VALUE
                Case 1
                    Cells(MY_ROWS, 18).Value = MY_COUNT
                Case "X"
                    Cells(MY_ROWS, 19).Value = MY_COUNT
                    Cells(MY_ROWS, 19).Interior.ColorIndex = 5 'blue
                Case 2
                    Cells(MY_ROWS, 20).Value = MY_COUNT
                    Cells(MY_ROWS, 20).Interior.ColorIndex = 7 'pink
            End Select
            MY_COUNT = 0
        End If
    Next MY_ROWS
End Sub

didn't realise you wanted the cells coloured, thought you were just showing the required results.

This will clear previous colouring.

Kishan · Sep 12, 2015

onlyadrafter said:
Hello,

does this work as expected?

didn't realise you wanted the cells coloured, thought you were just showing the required results.

This will clear previous colouring.

Hi onlyadrafter, your idea is even excellent it colours both side that is truly Fantastic!
Please check 1-Header Colour is going off, 2- result is not correct when I input 11 it is showing these result

	R	S	T
1	Before "X" 11th Position	Before "X" 11th Position	Before "X" 11th Position
2	Count 1	Count X	Count 2
3
4
5	3
6
7	3
8
9
10
11
12
13	3	2
14
15
16			4
17			2
18	4
19	2
20			2
21
22	4

Count 1-X-2 Before "X" 11th Pos

must show as per #post 1

Thank you for your kind help

Regards,
Kishan

Count 1-X-2 Before "X" 11th Pos

Kishan

Well-known Member

Excel Facts

onlyadrafter

Well-known Member

Kishan

Well-known Member

Kishan

Well-known Member

onlyadrafter

Well-known Member

MickG

MrExcel MVP

Kishan

Well-known Member

MickG

MrExcel MVP

onlyadrafter

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Count 1-X-2 Before "X" 11th Pos

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

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