The prompt has these issues:
- OUTPUT section is duplicated — appears twice, remove one
- Sanskrit still in point 2 but removed from subjects list — fix to match
- Music still in Subject Variations — needs to be removed since you excluded it
- N/A rule missing — if subject absent in both files
- AB rule split across two lines — consolidate into one clear rule
- "Claude defines summary" line removed but Sheet 2 structure is now back — good, keep it
Final Clean Prompt:
I have two files for Class IX-C, Session 2025-26:
· Excel file: IX-C_Result_2025-26__.xlsx
· PDF file: Report_Card.pdf — 41 individual report cards, one per page
STUDENTS TO SKIP
Exclude Roll Numbers 2, 14, 28, 39 — they are CWSN students. Do not compare them, do not flag them as missing. A separate Excel will be provided for them.
FILE STRUCTURE
Excel — "Accumulative Result" sheet:
· Each subject has 3 columns: [Theory 80] | [Internal Assessment 20] | [Total 100]
· Subjects to compare: English, Hindi, Maths, Science, Social Science, French, Hindi Additional, PT, AI
· Also has: Grand Total (G.T.) out of 500
Excel — "Remarks" sheet:
· Contains: Attendance, Percentage (%), Remarks/Result for each student
· Match to PDF by Roll Number
PDF — Report Card (per student page):
· Admission No. shown on top left
· Annual Theory Weightage = 50 marks
· Term-I Theory Weightage = 30 marks
· Combined Theory (80 marks) = Annual Theory Weightage (50) + Term-I Theory Weightage (30)
· Internal Assessments = 20 marks
· Total Marks = 100
· Overall Total, Overall Percentage, Attendance shown at bottom
WHAT TO COMPARE
1. Admission Number Compare Admission No. from Excel vs PDF top section. Flag any mismatch.
2. Theory Marks out of 80 PDF: Annual Theory Weightage (50) + Term-I Theory Weightage (30) = 80 Excel: [Theory 80] column Compare for: English, Hindi, Maths, Science, Social Science, French, Hindi Additional
3. Internal Assessment (20 marks) Compare PDF Internal Assessments vs Excel [Internal Assessment 20] For: English, Hindi, Maths, Science, Social Science, French, Hindi Additional
4. Total Marks (100) Compare PDF Total Marks vs Excel [Total 100] For: English, Hindi, Maths, Science, Social Science, French, Hindi Additional
5. AI / PT (Practical Subjects — Total 100 only) These subjects have a different structure — do NOT apply the 50+30 theory formula. Compare Total Marks (100) only — PDF Total Marks vs Excel [Total 100]. Identify per student whether their practical subject is AI or PT from the PDF and compare accordingly. If neither AI nor PT is present for a student, mark as N/A.
6. Grand Total Compare PDF Overall Total vs Excel G.T. column
7. Percentage Compare PDF Overall Percentage vs Excel % from Remarks sheet. Round both to 1 decimal place before comparing.
8. Remarks / Result Compare PDF Result field vs Excel Remarks from Remarks sheet.
9. Attendance Compare PDF Attendance vs Excel Attendance from Remarks sheet.
SUBJECT VARIATIONS (handle carefully)
· Some students take French or Hindi Additional instead of Hindi — identify from PDF, do not flag as mismatch for missing Hindi
· Some students take AI or PT as their practical subject — identify per student from PDF
· If a subject is absent in both files for a student — mark N/A
· If a subject is present in one file but missing in the other — mark ❓ Missing
· AB rule: If a value is "AB" in either PDF or Excel — always flag it with orange highlight. AB must never be treated as 0 or passed silently.
COLOR CODING (apply to all Status columns)
· 🟢 Green = ✔ Match
· 🔴 Red = ✘ Mismatch (show difference value)
· 🟡 Yellow = ⚠ N/A (subject not applicable for this student)
· 🟠 Orange = ❓ Missing or AB value
OUTPUT: Excel file with 2 sheets
Sheet 1 — "Detailed Comparison" Columns: Roll No | Adm No (Excel) | Adm No (PDF) | Adm No Status | Student Name | Subject | Theory 80 (Excel) | Theory 80 (PDF) | Theory Status | Int 20 (Excel) | Int 20 (PDF) | Int Status | Total 100 (Excel) | Total 100 (PDF) | Total Status | GT (Excel) | GT (PDF) | GT Status | % (Excel) | % (PDF) | % Status | Remarks (Excel) | Remarks (PDF) | Remarks Status | Attendance (Excel) | Attendance (PDF) | Attendance Status
Sheet 2 — "Mismatch Summary"
· List only students who have at least one mismatch, AB flag, or missing value
· Columns: Roll No | Name | Field with Mismatch | Excel Value | PDF Value | Difference
· At bottom: total counts of Match / Mismatch / N/A / Missing / AB
RULES
· Match students by admission no. as primary key, verify with first name
· Do not skip any student except Roll No. 2, 14, 28, 39
· Show all 37 remaining students even if all fields match
· Do not leave any subject row blank — always show a status
· AB must always be flagged — never treat as zero or pass silently
No comments:
Post a Comment