How to Extract & Count Text Values from a Huge Range that includes Blanks and Several Instances of Each

[harrypanaich]

Hi there, please help me S.O.S. I have a table that lists 1000+ individual student details under columns labelled as ClassID, Name, ClassRoomNo, Subject1, Subject2, Subject3,.... Subject9. The subjects are text entries such as 'Psy', 'Latin', 'Math', 'Art', 'French', ... and so on. Each student has a different combination of one or several subjects upto a maximum of nine subjects; blanks also exist where a student may have taken up less than nine subjects. I need to reorganize the table on the fly so that I can see details of the number of students for each ClassRoomNo who have taken each of the numerous subjects. How can I do it. I have tried arrays, pivot tables and what not. I am a beginner at best.

 

[AliGW]

Are you wanting to generate this table automatically, or will you create it manually and then fill in the numbers using formulae?

 

[Ombir]

Can you post sample input data and expected output or link to download workbook ?

 

[Ombir]

Assuming your table structure is similar that is shown below:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	ClassId	Name	Class Room no	Sub1	Sub2	Sub3	Sub4	Sub5	Sub6	Sub7	Sub8	Sub9
2	10	ABC1	10A	ENG	FRE	LAT	MAT	ART	PSY	SCT	CHE	BIO
3	11	ABC2	11A	ART	PSY	SCT	CHE	BIO	ENG	FRE	LAT	MAT
4	12	ABC3	12A	FRE	LAT	MAT	CHE	BIO	ENG	ART	PSY	SCT
5	10	ABC4	10A	MAT	CHE	FRE	LAT	ART			ENG	SCT
6	10	ABC5	10A	ART	PSY	SCT	CHE	BIO	ENG	FRE	LAT	MAT
7	10	ABC6	10A	FRE	LAT	MAT	CHE		ENG	ART	PSY	SCT
8	10	ABC7	10A	MAT	CHE	FRE	LAT	ART	PSY	BIO	ENG	SCT
9	10	ABC8	10A	ART	PSY	SCT	CHE	BIO	ENG	FRE	LAT	MAT
10	11	ABC9	11A	FRE	LAT	MAT	CHE	BIO	ENG	ART	PSY	SCT
11	12	ABC10	12A	ENG	FRE	LAT	MAT	ART	PSY	SCT	CHE	BIO
12	11	ABC11	11A	ART	PSY	SCT	CHE	BIO	ENG	FRE	LAT
13	12	ABC12	12A	FRE		MAT	CHE	BIO	ENG	ART		SCT
14	12	ABC13	12A	MAT	CHE	FRE	LAT	ART	PSY	BIO	ENG	SCT
Sheet1

And I am expecting your output as this:

Book1
	N	O	P
1	Class Room No	Sub	Count
2	10A	ENG	6
3	10A	FRE	6
4	10A	LAT	6
5	10A	MAT	6
6	10A	ART	6
7	10A	PSY	5
8	10A	SCT	6
9	10A	CHE	6
10	10A	BIO	4
11	11A	ART	3
12	11A	PSY	3
13	11A	SCT	3
14	11A	CHE	3
15	11A	BIO	3
16	11A	ENG	3
17	11A	FRE	3
18	11A	LAT	3
19	11A	MAT	2
20	12A	FRE	4
21	12A	LAT	3
22	12A	MAT	4
23	12A	CHE	4
24	12A	BIO	4
25	12A	ENG	4
26	12A	ART	4
27	12A	PSY	3
28	12A	SCT	4
Sheet1

You can try this code which was provided to me by Mick somedays ago:

Sub ClassCnt()
Dim Ray As Variant, Rw As Long, Ac As Long, n As Long, Dic As Object, Q As Variant
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Ray = Range("C1:L" & lr)
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 3)
nray(1, 1) = "Class Room No": nray(1, 2) = "Sub": nray(1, 3) = "Count"
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
n = 1
tt = Timer

For Rw = 2 To UBound(Ray, 1)
    For Ac = 2 To UBound(Ray, 2)
      If Len(Ray(Rw, Ac)) <> 0 Then
        If Not Dic.Exists(Ray(Rw, 1) & Ray(Rw, Ac)) Then
            n = n + 1
            nray(n, 1) = Ray(Rw, 1): nray(n, 2) = Ray(Rw, Ac): nray(n, 3) = 1
            Dic.Add Ray(Rw, 1) & Ray(Rw, Ac), Array(n, 1)
        Else
            Q = Dic(Ray(Rw, 1) & Ray(Rw, Ac))
                Q(1) = Q(1) + 1
                nray(Q(0), 3) = Q(1)
           Dic(Ray(Rw, 1) & Ray(Rw, Ac)) = Q
        
        End If
     End If
    Next Ac
Next Rw
Range("N1").Resize(n, 3) = nray
MsgBox "Code took " & Format(Timer - tt, "0.000secs")
End Sub

 

[harrypanaich]

Thanks Ombir, that worked. My apologies for a delayed acknowledgment. I will try and tweak it so that the output os grouped as per ClassRoomNo. Will get back as soon as I try. Am really grateful and amazed. Thanks a pile again.