Need help on vba code/excel formula to use!

[ynyend1]

Hello all
Greetings!
I have attached a workbook here containing sample data and clear explanation of what I need.
This is what I want to achieve:

I need an Excel formula/VBA Code that will do the following:
1: Concatenate the SUBJECT NAME in the average results with the DYANAMIC LETTER GRADE which will change automatically as average marks changes for each subject as per the criteria given in column AO and AP.. Refer to the combined results column in the attached workbook for the output I want; then:
2: If the student does not take any of the above subjects then it should not concatenate that particular subject with its marks, it should leave it blank and proceed to the next subject; refer to the second student in the attached sheet (check the combined result for the second students, that's the output I want).

I will highly appreciate for your help!

SAMPLE FILE FOR FORMULA HELP.xlsx
	AA
3	39
Sheet1

Cell Formulas
Range	Formula
AA3	AA3	=IFERROR(AVERAGE(C3,O3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:AK4	Expression	=$A3=""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3:B4	List	ME,KE
C3:AK4	Whole number	between 0 and 100

 

[ynyend1]

Than

Thanks for updating your profile.

If you turned your criteria columns up the other way, might this formula work for you?

ynyend1.xlsm
	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1		AVERAGE RESULTS												CRITERIA
2	CIV	HIS	GEO	KISW	ENG	PHY	CHEM	BIOL	B/MATH	ARABIC	DINI	COMBINED RESULTS		LETTER GRADE	FROM	TO
3	38.5	66	37	65	54	46	28	50.5	88.5	70.5	76.5	CIV-'D', HIS-'B', GEO-'D', KISW-'B', ENG-'C', PHY-'C', CHEM-'F', BIOL-'C', B/MATH-'A', ARABIC-'B', DINI-'A'		F	0	29
4	39.5		61.5	77	81.5		66	71.5	55			CIV-'D', GEO-'C', KISW-'A', ENG-'A', CHEM-'B', BIOL-'B', B/MATH-'C'		D	30	44
5														C	45	64
6														B	65	74
7														A	75	100
Sheet3

Cell Formulas
Range	Formula
AL3:AL4	AL3	=TEXTJOIN(", ",1,IF(AA3:AK3="","",AA$2:AK$2&"-'"&INDEX(AN$3:AN$7,MATCH(AA3:AK3,AO$3:AO$7))&"'"))

Thank you very much! It has just worked after applying arrays!! One more question, will this formula still work if I send this template to someone using Microsoft 2007 version?

 

[maabadi]

Try This:

Sub AddCombinedResults()
Dim i As Long, j As Long, Lr As Long, R As String, S As String
Dim Arr As Variant, Sh As Worksheet, N As Long, ShNames As String
ShNames = "FORM I, FORM II, FORM III, FORM IV"
Arr = Split(ShNames, ", ")
For Each Sh In ThisWorkbook.Sheets
Lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
For N = 0 To UBound(Arr)
If Sh.Name = Arr(N) Then
For i = 3 To Lr
For j = 27 To 37
If Sh.Cells(i, j).Value = "" Then
Else
R = Application.WorksheetFunction.Index(Sh.Range("AN3:AN7"), Application.WorksheetFunction.Match(Sh.Cells(i, j), Sh.Range("AP3:AP7"), -1))
R = Sh.Cells(2, j).Value & "-'" & R & "'"
If S = "" Then
S = R & ", "
Else
S = S & R & ", "
End If
End If
Next j
Sh.Cells(i, 38).Value = Left(S, Len(S) - 2)
S = ""
Next i
End If
Next N
Next Sh

End Sub

 

[Peter_SSs]

Thank you very much! It has just worked

You're welcome.

will this formula still work if I send this template to someone using Microsoft 2007 version?

No. TEXTJOIN function only exists in Microsoft 365 and Excel 2019.
If it is to be used with 2007, go with the vba solution.

 

[ynyend1]

i

Try This:

Sub AddCombinedResults()
Dim i As Long, j As Long, Lr As Long, R As String, S As String
Dim Arr As Variant, Sh As Worksheet, N As Long, ShNames As String
ShNames = "FORM I, FORM II, FORM III, FORM IV"
Arr = Split(ShNames, ", ")
For Each Sh In ThisWorkbook.Sheets
Lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
For N = 0 To UBound(Arr)
If Sh.Name = Arr(N) Then
For i = 3 To Lr
For j = 27 To 37
If Sh.Cells(i, j).Value = "" Then
Else
R = Application.WorksheetFunction.Index(Sh.Range("AN3:AN7"), Application.WorksheetFunction.Match(Sh.Cells(i, j), Sh.Range("AP3:AP7"), -1))
R = Sh.Cells(2, j).Value & "-'" & R & "'"
If S = "" Then
S = R & ", "
Else
S = S & R & ", "
End If
End If
Next j
Sh.Cells(i, 38).Value = Left(S, Len(S) - 2)
S = ""
Next i
End If
Next N
Next Sh

End Sub

It only works on the sample workbook that I shared here. Once I go to my actual workbook sheets it doesn't work.
The criteria reference in my actual sheets are AW3:AW7 (for grades) and AY3:AY7 (for marks)
I have tried to replace the reference in the sample document ie AN3:AN7 with these references but seems not to work ok.
Also my original workbook do have some other sheets with other names in addition to those named FORM I, FORM II, FORM III, FORM IV. Can that be the causes for the Running time error 5 am getting?

 

[maabadi]

Also Change j Values Because It shows Column Numbers
Then j=27 to 37 is Columns AA:AK should be changed to your column Number
Also at Sh.Cells(i, 38).Value : 38 should be changed to your result Column Number.

 

[ynyend1]

Also Change j Values Because It shows Column Numbers
Then j=27 to 37 is Columns AA:AK should be changed to your column Number
Also at Sh.Cells(i, 38).Value : 38 should be changed to your result Column Number.

This have no problems. They are alike. The only difference is with next columns!
When I run the code I get the results only in Form I sheet but it also gives me an error of Run time error 5

 

[maabadi]

Are you pasted macro more than one time at Workbook.
This macro only need one time to paste and run.

 

[ynyend1]

Are you pasted macro more than one time at Workbook.
This macro only need one time to paste and run.

So should I paste only once in ThisWorkbook?

 

[maabadi]

Not in This workbook. Go to Insert Module and paste code only one time.
And Delete all codes with same name at workbook.

 

[ynyend1]

So should I paste only once in ThisWorkbook?