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

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
1YearDateP1P2P3P4P5P6P7P8P9P10P11P12P13P14EmptyBefore "X" 11th PositionBefore "X" 11th PositionBefore "X" 11th Position
2YearDateP1P2P3P4P5P6P7P8P9P10P11P12P13P14EmptyCount 1Count XCount 2
3201301/12/20132X2111X1112X11
4201302/12/201322X121XX21212X
5201303/12/201311111X1X11XX122
6201304/12/201311121112X111X1
7201305/12/20131X11X12X11111X
8201306/12/201311122X111X111X
9201307/12/201311X111121X11X1
10201308/12/2013121111211X1112
11201309/12/201322X2112X211X12
12201310/12/2013111X1221121X11
13201311/12/20131211X1111XX1XX1
14201312/12/2013112X2212X11X21
15201313/12/20131X1X1222111222
16201314/12/2013XX11211222X1113
17201315/12/201322222X1112X2X11
18201316/12/2013X111XXX111X1223
19201317/12/201322X1212121X11X1
20201318/12/2013X1121121X2X2211
21201319/12/2013111212X22X1211
22201320/12/20132111X211X11122
Count 1-X-2 Before "X" 11th Pos


Need VBA solution if possible

Please help!

Thanks

Regards,
Kishan
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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?
 
Upvote 0
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?
Click to expand...
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
 
Upvote 0
onlyadrafter said:
Hello,

You haven't specified what you want to do with the results. Also, your question is a bit confusing,
Click to expand...
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 11th 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 11th position counting C5 as 1st position “cell M5 will be the 11th 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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
onlyadrafter said:
Hello This gives the answers as per your example
Click to expand...

MickG said:
Try this:-
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

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

RST

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Before "X" 11th Position[/TD]
[TD="align: center"]Before "X" 11th Position[/TD]
[TD="align: center"]Before "X" 11th Position[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Count 1[/TD]
[TD="align: center"]Count X[/TD]
[TD="align: center"]Count 2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #0000FF, align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FF00FF, align: center"]4[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FF00FF, align: center"]2[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FF00FF, align: center"]2[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Count 1-X-2 Before "X" 11th Pos​



must show as per #post 1

Thank you for your kind help

Regards,
Kishan
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Excel Userform Listbox Search issues
Replies
3
Views
725
Akuini
Akuini
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
377
Akuini
Akuini
K
  • Question Question
Rolled up color coding
Replies
3
Views
232
felixstraube
F
A
  • Question Question
Store 1st result of iterative calc, then 2nd... to 200+ iterations
Replies
9
Views
412
myall_blues
myall_blues
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
809
juanam
J

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top