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

 

[maabadi]

Please show example Desired Result on Sheet Then Select All your Range Then Upload it with XL2BB ADDIN.

 

[ynyend1]

SAMPLE FILE FOR FORMULA HELP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1					MID TERM												TERMINAL											AVERAGE RESULTS												CRITERIA			I need a formula 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 sheet 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 as it is seen for the second student in the attached sheet (check the combined result for the second students, that;s the output I want).
2	STUDENT NAME	JINSIA	CIVICS	HISTORY	GEOGRAPHY	KISWAHILI	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY	B/MATHS	ARABIC	DINI	Column1	CIVICS	HISTORY	GEOGRAPHY	KISWAHILI	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY	B/MATHS	ARABIC	DINI	Column2	CIV	HIS	GEO	KISW	ENG	PHY	CHEM	BIOL	B/MATH	ARABIC	DINI	COMBINED RESULTS		LETTER GRADE	FROM	TO
3	ASHURA RAMADHANI	ME	23	43	54	65	65	67	34	56	87	98	88		54	89	20	65	43	25	22	45	90	43	65		39	66	37	65	54	46	28	51	89	71	77	CIV-'D', HIST-'B', GEO-'D', KISW-'B', ENGL-'C', PHY-'C', CHEM-'F', BIO-'C', B/MATH-'A', ARABIC -'B', DINI -'A'		A	75	100
4	KESIA SHADRACK	KE	45		67	87	87		76	54	43				34		56	67	76		56	89	67				40		62	77	82		66	72	55			CIV-'D', GEO-'C', KISW-'A', ENGL-'A', CHEM-'B', BIO-'B', B/MATH-'C'		B	65	74
5																																								C	45	64
6																																								D	30	44
7																																								F	0	29
Sheet1

Cell Formulas
Range	Formula
AE2:AF2,AA2:AC2	AA2	=LEFT(O2,3)
AD2,AG2:AH2	AD2	=LEFT(R2,4)
AI2	AI2	=LEFT(W2,6)
AJ2:AK2	AJ2	=LEFT(X2,7)
AA3:AK4	AA3	=IFERROR(AVERAGE(C3,O3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AL3:AL4	Expression	=$A3=""	text	NO
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]

Thank you very much Maabadi for your instruction on it!

Please show example Desired Result on Sheet Then Select All your Range Then Upload it with XL2BB ADDIN.

 

[maabadi]

Try This VBA Code:

Sub AddCombinedResults()
Dim i As Long, j As Long, Lr As Long, R As String, S As String
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To Lr
For j = 27 To 37
If Cells(i, j).Value = "" Then
Else
R = Application.WorksheetFunction.Index(Range("AN3:AN7"), Application.WorksheetFunction.Match(Cells(i, j), Range("Ap3:AP7"), -1))
R = Cells(2, j).Value & "-'" & R & "'"
If S = "" Then
S = R & ", "
Else
S = S & R & ", "
End If
End If
Next j
Cells(i, 38).Value = Left(S, Len(S) - 2)
S = ""
Next i
End Sub

 

[ynyend1]

Try This VBA Code:

Sub AddCombinedResults()
Dim i As Long, j As Long, Lr As Long, R As String, S As String
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To Lr
For j = 27 To 37
If Cells(i, j).Value = "" Then
Else
R = Application.WorksheetFunction.Index(Range("AN3:AN7"), Application.WorksheetFunction.Match(Cells(i, j), Range("Ap3:AP7"), -1))
R = Cells(2, j).Value & "-'" & R & "'"
If S = "" Then
S = R & ", "
Else
S = S & R & ", "
End If
End If
Next j
Cells(i, 38).Value = Left(S, Len(S) - 2)
S = ""
Next i
End Sub

This is Great! It works perfectly!
Be blessed abundantly!!

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

For example, if you have Excel 365 and were interested in a formula solution, you could try this.

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'		A	75	100
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'		B	65	74
5														C	45	64
6														D	30	44
7														F	0	29
Sheet1

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

 

[maabadi]

You're Welcome & Please upload your Account Details as Peter_SSs Said.
Thanks for Feedback also.

 

[ynyend1]

Try This VBA Code:

Sub AddCombinedResults()
Dim i As Long, j As Long, Lr As Long, R As String, S As String
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To Lr
For j = 27 To 37
If Cells(i, j).Value = "" Then
Else
R = Application.WorksheetFunction.Index(Range("AN3:AN7"), Application.WorksheetFunction.Match(Cells(i, j), Range("Ap3:AP7"), -1))
R = Cells(2, j).Value & "-'" & R & "'"
If S = "" Then
S = R & ", "
Else
S = S & R & ", "
End If
End If
Next j
Cells(i, 38).Value = Left(S, Len(S) - 2)
S = ""
Next i
End Sub

Hello, This formula worked perfectly in the sheet you attached, but when I apply it to other sheets I have for other classes I get the RESULTS correctly but with the following error message as attached in the image

What am I missing here?
Furthermore, is there a way that I can make a Button that will run the code throughout all my sheets named "FORM I, FORM II, FORM III, FORM IV"?
Please assist this one!
Thanks in advance!

 

[Peter_SSs]

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))&"'"))