is there a better way to do this ?

[palaeontology]

In column J (of a worksheet called 'SURVEY') I have the concatenation of question numbers that students got completely wrong in a 2-paper exam.

for example ... P1 Q 18a ... refers to Paper 1 Question 18a
for example ... P2 Q 6c ... refers to Paper 2 Question6c

There are about 250 students who do the subject, so there are about 250 of these rows (each one specific to an individual student.

In a worksheet called 'Lookups', I have a table indicating which textbook chapter unit each question draws upon for its content

Currently I have a spreadsheet that successfully compares a student list of questions they got completely wrong with the table showing what textbook chapter units were required to answer those questions and returns an ordered (by both Chapter number and by chapter sub-unit) unique list of textbook chapter units that each individual student (for want of a better expression) has no idea about, which I call 'Areas Requiring Immediate Attention' ... see below ...

The problem is, the file is rather big at 27MB and is therefore very slow to run and saving any changes is a nightmare to wait around for.

I currently achieve it by using about 1400 columns to ...
* split each students' column J concatenated list into separate cells ... for example 'P1 Q 14e' would get its own cell ... and as I can't predict how many questions a student is going to get completely wrong, I've had to allow for a very large number of columns that would be used in un-concatenating the students' list
* converted each of the returned textbook chapter subunits into individual 4-digit codes because I later sort them into an ordered list
* I then return a unique list with any repeated 4-digit codes removed
* I then sort this unique list
* I then convert these 4-digit codes back into their textbook chapter units
* I then concatenate this (possibly) long list into one cell for use in a Form I then send each student with their individualised list.

All of this is done by formula .... I haven't left anything to be done manually, as it has to be able to be used by others who may have no excel understanding.

Can anyone think of a way to do this far more efficiently than my clunky system ?

Very kind regards,

Chris

 

[Alex Blakenburg]

I don't know what time zone johnnyL is in and I expect that he will answer you overnight. (I am like you in Australia)
I am pretty sure most of the size savings where on the sheets Paper 1 & Paper 2.

On your original file go to each of those sheets and hit Ctrl+End and see where that takes you.
Then do the same on johnnyL's cleaned up file.

You should find that on your files it goes does to row last row on the sheet ie > 1million and on his version it goes down to < 130 rows.
This is done by deleting rows which had no data and just a formula in the row.
On Paper 1 you will need an extra step (refer Fluff's row height suggestion).
The change won't be reflected until you hit Save on the spreadsheet.

 

[johnnyL]

Hi JohnnyL,

you uploaded a link to a 'shortened version' of my original file, but when I look at it, I can't see any internal differences, though it's now sitting at a much more manageable 10MB.

You right still too big. Smaller Version Just over 6MB

 

[Michael M]

@johnnyL
I couldn't acces the lates version OR the website for that matter....keep getting failed connection !
As a matter of interest did you save the file as an .xlsb to reduce the size ??

 

[johnnyL]

Link is still valid. Yes it was saved as xlsb after reducing the file further with the help of suggestions by @Fluff

.xlsb reduced file size by about 1MB, could easily be saved back as .xlsx format if desired.

I haven't done anything with deleting columns and such.

 

[Michael M]

Yeah, big improvement....should make the OP happy !!