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
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
