Look for name in row then count categories below it and add them up

[Nick70]

Hi,

I have a set of data under different analyst names in Sheet2 and Sheet3.

Something like below:

John Lucy

pass fail
fail pass
pass error
error error
pass pass
fail pass

I would like to have a macro that summarizes data under John and Lucy (found in Sheets 2 and 3) and gives the totals in Sheet1.

So for example in Sheet1 we will have totals:
John
pass 3
fail 2
error 1

Lucy
pass 3
fail 1
error 2

The analyst names are found in Row 9 of Sheets 2 and 3 (let's say a range K9 to O9)
The data to be summarized will be under those names (let's say in range K29:O50)

How can I summarize in Sheet1 how many pass, fails, errors are under John and how many under Lucy if the analyst names are found in range K9 to O9 of Sheet2 and Sheet3 and the data to add up is found under the analysts names in range K29:O50 of Sheet2 and Sheet3?

I would like to have macro and not use a Pivot.

Thanks,
N.

 

[Elaszat]

Hi Nick,

does it have to be a VBA macro or would you be interested in a solution with a formula?

Regards,
Elaszat

 

[Nick70]

formula also fine!

 

[DanteAmor]

If your data is like this on sheets 2 and 3

Book1
	A										K	L	M	N	O
1
8
9											John1	John2	John3	John4	John5
10											pass	fail	pass	error	pass
11											fail	pass	error	pass	fail
12											pass	error	pass	fail	pass
13											error	pass	fail	pass	fail
14											pass	fail	pass	fail	pass
15											fail	pass	fail	pass	fail
Sheet2

Book1
	A										K	L	M	N	O
1
8
9											Lucy1	Lucy2	Lucy3	Lucy4	Lucy5
10											pass	fail	pass	error	pass
11											fail	pass	error	pass	fail
12											pass	error	pass	fail	pass
13											error	pass	fail	pass	fail
14											pass	fail	pass	fail	pass
15											fail	pass	fail	pass	fail
Sheet3

Try this macro, the results will be on sheet1 in columns A and B

Book1
	A	B
1
2	John1
3	pass	3
4	fail	2
5	error	1
6
7	John2
8	fail	2
9	pass	3
10	error	1
11
12	John3
13	pass	3
14	error	1
15	fail	2
16
17	John4
18	error	1
19	pass	3
20	fail	2
21
22	John5
23	pass	3
24	fail	3
25
26	Lucy1
27	pass	3
28	fail	2
29	error	1
30
31	Lucy2
32	fail	2
33	pass	3
34	error	1
35
36	Lucy3
37	pass	3
38	error	1
39	fail	2
40
41	Lucy4
42	error	1
43	pass	3
44	fail	2
45
46	Lucy5
47	pass	3
48	fail	3
Sheet1

Sub count_categories()
  Dim sh1 As Worksheet, sh2 As Worksheet, dic As Object
  Dim a() As Variant, b() As Variant, ky As Variant, arr As Variant, sh As Variant
  Dim n As Long, i As Long, j As Long
 
  Set sh1 = Sheets("Sheet1")
  Set dic = CreateObject("Scripting.Dictionary")
 
  sh1.Range("A2:B" & Rows.Count).ClearContents
  arr = Array("Sheet2", "Sheet3")
  n = 1
 
  For Each sh In arr
    
    Set sh2 = Sheets(sh)
    b = sh2.Range("K9", sh2.Range("O" & sh2.Range("O" & Rows.Count).End(xlUp).Row)).Value
    If n = 1 Then ReDim a(1 To (UBound(b, 1) * UBound(b, 2)) * (UBound(arr) + 10), 1 To 2)
    
    For j = 1 To UBound(b, 2)
      dic.RemoveAll
      For i = 2 To UBound(b, 1)
        dic(b(i, j)) = dic(b(i, j)) + 1
      Next i
      a(n, 1) = b(1, j)
      n = n + 1
      For Each ky In dic.keys
        a(n, 1) = ky
        a(n, 2) = dic(ky)
        n = n + 1
      Next ky
      n = n + 1
    Next j
    
  Next sh
 
  sh1.Range("A2").Resize(n, 2).Value = a()
End Sub

 

[Elaszat]

Hi,

after seeing the code by @DanteAmor I'm not sure I understood your request porperly. But here is what I have come up with:

Book1
	A	B	C	D	E	F
1	John	Lucy	Adam	Steve	Aubrey	pass
2	pass: 8	pass: 9	pass: 6	pass: 7	pass: 7	fail
3	fail: 9	fail: 4	fail: 8	fail: 7	fail: 6	error
4	error: 5	error: 9	error: 8	error: 8	error: 9
Sheet1

Cell Formulas
Range	Formula
A2:E4	A2	=IF(A$1=HLOOKUP(A$1,Sheet2!$K$9:$O$9,1,FALSE),COUNTIF(OFFSET(Sheet2!$K$29:$O$50,0,COLUMN(A$1)-1,,1),$F1))

The data so far is in Sheet 2, like this:

Book1
	K	L	M	N	O
9	John	Lucy	Adam	Steve	Aubrey
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29	error	pass	fail	fail	fail
30	fail	fail	pass	error	pass
31	fail	fail	error	error	error
32	pass	pass	pass	error	pass
33	pass	error	fail	pass	error
34	error	error	fail	error	error
35	error	error	fail	error	pass
36	fail	pass	pass	pass	error
37	pass	error	fail	pass	pass
38	error	pass	pass	pass	error
39	fail	error	error	fail	error
40	error	error	error	pass	pass
41	pass	pass	pass	pass	pass
42	pass	fail	error	fail	pass
43	pass	pass	fail	pass	fail
44	fail	fail	fail	error	fail
45	fail	error	fail	error	fail
46	pass	error	error	fail	fail
47	fail	pass	error	fail	error
48	fail	pass	error	fail	error
49	fail	pass	error	error	fail
50	pass	error	pass	fail	error
Sheet2

Regards,
Elaszat

 

[Nick70]

Thanks a lot to both!!
N.
??

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Elaszat]

You're welcome and yeah, thanks for the feedback.