Need help with Excel Logic for Student Database

mileskh

New Member
Joined
Feb 6, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am in the process of building a student database on excel which includes 30+ classes and 4 variables per class namely, 1. enrolled date, 2. city, 3. country, and 4. payment method.
As this database will be used by a company that has no employee who can use excel well, I will have to avoid using pivot tables.
They also want a student finder page, where you enter the student's email and it will return all the necessary information for the student including their city and country which they enrolled their last class from as their latest updated city, country, and payment method.

How I have set up right now is to manually enter/copy-paste the data from CSV files for each class into the database using power pivot and link the "finder" page with the database page with vlookup.
My current problem is that when determining each student's latest updated city and country based on the latest enrollment date, I have resulted to using max function for each enrolled date cell and linking it with a very long If function which checks each enrolled date from 31 different classes, which causes a lot of performance problems.

This is how I have set up the database sheet based on their request:
Student Email Student Name Latest Sign Up Date City Country Payment Method | 1. Enrolled Date 1. City 1. Country 1. Payment Method | 2. Enrolled Date 2.City 2. Country 2. Payment

test@gmail.com John Doe "Function 1" "Function 2" "Function 3" "Function 4" | 1-Jan-2021 LA USA BoA | 1-Jan-2022 NYC USA Chase


Function 1 =Max(@1.Enrolled Date, @2. Enrolled Date.....)


Function 2 =IF(@Latest Signup Date = 1. Enrolled Date, 1. City, IF(@Latest Sign up Date = 2. Enrolled Date, 2. City,...)

Function 3 =IF([@[Latest Signup Date]]=[@[1. Enrolled Date]],[@[1. Country]],IF([@[Latest Signup Date]]=[@[2. Enrolled Date]],[@[2. Country]],IF([@[Latest Signup Date]]=[@[3. Enrolled Date]],[@[3. Country]],IF([@[Latest Signup Date]]=[@[4. Enrolled Date]],[@[4. Country]],IF([@[Latest Signup Date]]=[@[5. Enrolled Date]],[@[5. Country]],IF([@[Latest Signup Date]]=[@[6. Enrolled Date]],[@[6. Country]],IF([@[Latest Signup Date]]=[@[7. Enrolled Date]],[@[7. Country]],IF([@[Latest Signup Date]]=[@[8. Enrolled Date]],[@[8. Country]],IF([@[Latest Signup Date]]=[@[9. Enrolled Date]],[@[9. Country]],IF([@[Latest Signup Date]]=[@[10. Enrolled Date]],[@[10. Country]],IF([@[Latest Signup Date]]=[@[11. Enrolled Date]],[@[11. Country]],IF([@[Latest Signup Date]]=[@[12. Enrolled Date]],[@[12. Country]],IF([@[Latest Signup Date]]=[@[13. Enrolled Date]],[@[13. Country]],IF([@[Latest Signup Date]]=[@[14. Enrolled Date]],[@[14. Country]],IF([@[Latest Signup Date]]=[@[15. Enrolled Date]],[@[15. Country]],IF([@[Latest Signup Date]]=[@[16. Enrolled Date]],[@[16. Country]],IF([@[Latest Signup Date]]=[@[17. Enrolled Date]],[@[17. Country]],IF([@[Latest Signup Date]]=[@[18. Enrolled Date]],[@[18. Country]],IF([@[Latest Signup Date]]=[@[19. Enrolled Date]],[@[19. Country]],IF([@[Latest Signup Date]]=[@[20. Enrolled Date]],[@[20. Country]],IF([@[Latest Signup Date]]=[@[21. Enrolled Date]],[@[21. Country]],IF([@[Latest Signup Date]]=[@[22. Enrolled Date]],[@[22. Country]],IF([@[Latest Signup Date]]=[@[23. Enrolled Date]],[@[23. Country]],IF([@[Latest Signup Date]]=[@[24. Enrolled Date]],[@[24. Country]],IF([@[Latest Signup Date]]=[@[25. Enrolled Date]],[@[25. Country]],IF([@[Latest Signup Date]]=[@[26. Enrolled Date]],[@[26. Country]],IF([@[Latest Signup Date]]=[@[27. Enrolled Date]],[@[27. Country]],IF([@[Latest Signup Date]]=[@[28. Enrolled Date]],[@[28. Country]],IF([@[Latest Signup Date]]=[@[29. Enrolled Date]],[@[29. Country]],IF([@[Latest Signup Date]]=[@[30. Enrolled Date]],[@[30. Country]],IF([@[Latest Signup Date]]=[@[31. Enrolled Date]],[@[31. Country]],IF([@[Latest Signup Date]]=[@[32. Enrolled Date]],[@[32. Country]],IF([@[Latest Signup Date]]=[@[33. Enrolled Date]],[@[33. Country]],0)))))))))))))))))))))))))))))))))

Function 4 =IF(@Latest Signup Date = 1. Enrolled Date, 1. Payment Method, IF(@Latest Sign up Date = 2. Enrolled Date, 2. Payment Method,...)

So as you can see Function 1, 2, 3, and 4, needs to be applied to each row (each student) and the database will have over 10k students when completed.

How can I make the whole process more efficient and more performance-friendly for the pc?
Any help would be highly appreciated :'D
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,318
Messages
5,853,239
Members
431,557
Latest member
rajkusha

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