Count followed by one unique result.

[Kishan]

Using Excel 2000

Hi,

I got result data in cells B8:B93
In the cell E7 Got 3-X,
In the cells D8:D16 got 10 different types of results, which can be followed by, 3-X in the column B, I need to count followed result in cells E8:E16

Example 1-1 is followed 3 times by 3-X in column B, 2-1 is followed 2 times. And so on....

Example data...

Book1
	A	B	C	D	E	F
1
2
3
4
5
6
7		Result		10 Diff Results	Counts Followed By 3-X
8		3-X		0
9		2-X		1-1	3
10				1-X
11		3-X		1-2
12		2-X		2-1	2
13				2-X	11
14		3-X		2-2
15		2-X		3-X	17
16				3-2
17		3-X
18		2-X
19
20		3-X
21		2-X
22
23		3-X
24		1-1
25
26		3-X
27		3-X
28		3-X
29
30		3-X
31		3-X
32
33		3-X
34		3-X
35
36		3-X
37		2-X
38
39		3-X
40		2-X
41
42		3-X
43		3-X
44		3-X
45		2-1
46
47		3-X
48		1-1
49
50		3-X
51		3-X
52		3-X
53
54		3-X
55		3-X
56
57		3-X
58		2-X
59
60		3-X
61		2-1
62
63		3-X
64		3-X
65
66		3-X
67		2-X
68
69		3-X
70		2-X
71
72		3-X
73		3-X
74		3-X
75
76		3-X
77		3-X
78
79		3-X
80		3-X
81
82		3-X
83		3-X
84		3-X
85		1-1
86
87		3-X
88		2-X
89
90		3-X
91		3-X
92
93		3-X
94
95
96
Sheet1

Thank you in advance

Regards,
Kishan

 

[Eric W]

You really should look into learning some basic VBA yourself. The kind of problems you post are EASY! to code. With just a very basic knowledge of VBA, you could write your macros in less time than it takes to put up a post asking someone else to do it.

Sub CountX()Dim MyRow As Long, ResultRow As Long

    Range("E8:E16").ClearContents
    
    For MyRow = 8 To Cells(Rows.Count, "B").End(xlUp).Row
        If Cells(MyRow, "B") = "3-X" Then
            For ResultRow = 8 To 16
                If CStr(Cells(MyRow + 1, "B")) = CStr(Cells(ResultRow, "D")) Then
                    Cells(ResultRow, "E") = Cells(ResultRow, "E") + 1
                    Exit For
                End If
            Next ResultRow
        End If
    Next MyRow
    
End Sub

The above macro took me less than 3 minutes to write and test. I wrote a slightly shorter, more efficient version, but I'm posting this version because it just uses basic functions. I'll bet that even if you have no programming experience, you can figure out what most of the lines do. This site really isn't designed to teach VBA programming, but if you Google "Beginning VBA" you'll find a lot of resources. And if you have specific questions about VBA programming, you can come here to ask.

Good luck.

 

[Kishan]

Sub CountX()Dim MyRow As Long, ResultRow As Long

    Range("E8:E16").ClearContents
    
    For MyRow = 8 To Cells(Rows.Count, "B").End(xlUp).Row
        If Cells(MyRow, "B") = "3-X" Then
            For ResultRow = 8 To 16
                If CStr(Cells(MyRow + 1, "B")) = CStr(Cells(ResultRow, "D")) Then
                    Cells(ResultRow, "E") = Cells(ResultRow, "E") + 1
                    Exit For
                End If
            Next ResultRow
        End If
    Next MyRow
    
End Sub

HI Eric, thank you so much for giving a spot on solution. It's working fine and giving a desire results as request.

You really should look into learning some basic VBA yourself. The kind of problems you post are EASY! to code. With just a very basic knowledge of VBA, you could write your macros in less time than it takes to put up a post asking someone else to do it.

Eric, really I don't have any knowledge about "abc" of Visual Basic. I wish if I could, so not to give trouble anyone. I find it difficult for me, don't know when I will learn coding?

The above macro took me less than 3 minutes to write and test. I wrote a slightly shorter, more efficient version, but I'm posting this version because it just uses basic functions. I'll bet that even if you have no programming experience, you can figure out what most of the lines do. This site really isn't designed to teach VBA programming, but if you Google "Beginning VBA" you'll find a lot of resources. And if you have specific questions about VBA programming, you can come here to ask.

Good luck.

Hats off to you experts are solving the quires and giving perfect solutions, which makes easy to analysis data quicker and save a lot of time, and gave more time to spend with family. I will Google "Beginning VBA" and try to learn if I can.

Thank you for all your help

Good luck.

Kind Regards,
Kishan

 

[markmzz]

Using Excel 2000

Hi,

I got result data in cells B8:B93
In the cell E7 Got 3-X,
In the cells D8:D16 got 10 different types of results, which can be followed by, 3-X in the column B, I need to count followed result in cells E8:E16

Example 1-1 is followed 3 times by 3-X in column B, 2-1 is followed 2 times. And so on....

Thank you in advance

Regards,
Kishan

Hi!

Try the formulas below too:

=SUMPRODUCT(--(B$8:B$93=D8),--(B$7:B$92=RIGHT($E$7,3)))

Or

Use Ctrl+Shift+Enter to entre the formula

=SUM(IF(B$8:B$93=D8,IF(B$7:B$92=RIGHT($E$7,3),1)))


Markmzz