Need help on vba code/excel formula to use!

ynyend1

New Member
Joined
Jul 12, 2013
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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
339
Sheet1
Cell Formulas
RangeFormula
AA3AA3=IFERROR(AVERAGE(C3,O3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:AK4Expression=$A3=""textNO
Cells with Data Validation
CellAllowCriteria
B3:B4ListME,KE
C3:AK4Whole numberbetween 0 and 100
 

ynyend1

New Member
Joined
Jul 12, 2013
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
Than
Thanks for updating your profile. (y)

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

ynyend1.xlsm
AAABACADAEAFAGAHAIAJAKALAMANAOAP
1AVERAGE RESULTSCRITERIA
2CIVHISGEOKISWENGPHYCHEMBIOLB/MATHARABICDINICOMBINED RESULTSLETTER GRADEFROMTO
338.566376554462850.588.570.576.5CIV-'D', HIS-'B', GEO-'D', KISW-'B', ENG-'C', PHY-'C', CHEM-'F', BIOL-'C', B/MATH-'A', ARABIC-'B', DINI-'A'F029
439.561.57781.56671.555CIV-'D', GEO-'C', KISW-'A', ENG-'A', CHEM-'B', BIOL-'B', B/MATH-'C'D3044
5C4564
6B6574
7A75100
Sheet3
Cell Formulas
RangeFormula
AL3:AL4AL3=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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try This:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 12, 2013
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
i
Try This:
VBA Code:
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 12, 2013
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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
1620485037380.png
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Not in This workbook. Go to Insert Module and paste code only one time.
And Delete all codes with same name at workbook.
 

ynyend1

New Member
Joined
Jul 12, 2013
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
So should I paste only once in ThisWorkbook?
 

Forum statistics

Threads
1,136,300
Messages
5,674,962
Members
419,536
Latest member
Mohammed Jaffer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top