display results from multiple criteria

[kayza]

Hi excel master.
I need help to solve the case of searching and displaying data based on multi criteria using VBA, from the following example:

	A	B
1	100	S
2	100	S
3	100	J
4	103	S
5	103	J
6	103	S
7	104	J
8	104	J
9	104	S
10	...	...

<tbody>
</tbody>

and here are the results I want to show in column F,G based on critera "S" in column B:

F	G
100	2
103	2
104	1
...	...

<tbody>
</tbody>

thank you in advance, for your help

 

[reberryjr]

What is the logic in determining the values in F and G?

 

[kayza]

Hi reberryjr
sorry in advance, i am wrong in giving samples of data and this correct sample data

	A	B
1	int	cd
2	100	S
3	100	S
4	100	J
5	103	S
6	103	J
7	103	S
8	104	J
9	104	J
10	104	S
11	...

<tbody>
</tbody>

the values in column F are based on the same data contained in column A, while the results in column G are based on the criterion value "S"


Here Is my crude vba code, which can only display the values in column F, but if combined with the "S" criteria (based on column B) the result does not match what I expect

For n = 2 To 9
a = Cells(n, 1)
ur = Cells(Rows.Count, 6).End(xlUp).Row

[I][COLOR=#000000]'If Cells(n - 1, 1) = a And cells(n-1,2)="S" then '--?[/COLOR][/I]
If Cells(n - 1, 1) = a Then
    Cells(ur, 6) = Cells(n, 1)
    Cells(ur, 7) = Cells(ur, 7) + 1
    Else
    Cells(ur + 1, 6) = Cells(n, 1)
    Cells(ur + 1, 7) = Cells(ur + 1, 7) + 1
End If
Next

 

[reberryjr]

So, for row 6 and 7, I assume that you expect to see 103 in column F. What are you expecting to see in column G?

 

[kayza]

the result in column G is the sum of the criteria for value 100, 103, and 104 (column A) and the "S" criterion (column B).

F G
100 2
103 2
104 1

 

[reberryjr]

If you're trying to Sum A and B, then the value in B (J or S) has to have a value. Does J = 1 and S = 2?