Count followed by one unique result.

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEF
1
2
3
4
5
6
7Result10 Diff ResultsCounts Followed By 3-X
83-X0
92-X1-13
101-X
113-X1-2
122-X2-12
132-X11
143-X2-2
152-X3-X17
163-2
173-X
182-X
19
203-X
212-X
22
233-X
241-1
25
263-X
273-X
283-X
29
303-X
313-X
32
333-X
343-X
35
363-X
372-X
38
393-X
402-X
41
423-X
433-X
443-X
452-1
46
473-X
481-1
49
503-X
513-X
523-X
53
543-X
553-X
56
573-X
582-X
59
603-X
612-1
62
633-X
643-X
65
663-X
672-X
68
693-X
702-X
71
723-X
733-X
743-X
75
763-X
773-X
78
793-X
803-X
81
823-X
833-X
843-X
851-1
86
873-X
882-X
89
903-X
913-X
92
933-X
94
95
96
Sheet1


Thank you in advance

Regards,
Kishan
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

Code:
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.
 
Last edited:
Upvote 0
Code:
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

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