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

 

[Michael M]

Couple of quick notes...
The formula that contains a reference to another workbook...is that other workbook open and / or would it be possible to put the refrenced worksheet into your own workbook ?
AND
Have you tried turning calculation to Manual at the opening of the file and then turn it back to Automatic before saving ?

 

[johnnyL]

Well I took a short look at the file, and there are formulas EVERYWHERE.

Formulas are my achiles heel so perhaps some of the people that are stronger in that area can give advice on those.

Some that appear to be volatile to semi volatile:

Vlookup
Lookups sheet - Columns I,Y-AD
Waiting on ... sheet - Columns D, I, ADA-AHV
0<marks<Full (by STUDENT) sheet - Columns ADA-AHV

Indirect
Areas of Concern (by CLASS) sheet - Columns BG-DI

 

[Alex Blakenburg]

Lookups (2)
- has external file references - do you need these or can you pull the data into the workbook.
They tend to slow the saves and open down a lot and I suspect don't work for others anyway.

Paper1 & Paper2 have formulas down to row 65k.
- it is a simple formula and may not make much difference but do you need to cover that many rows.

The 2 Marks sheets (convert questions to chapters)
a) Existing
Am I missing something or is there a whole heap of double handling happening here ?

Column BT - EE have separate columns for the values​

Column EF Concatenates those numbers​

Why are columns EH - GR then splitting Column EF back to individual columns ?
(the results look identical to BT - EE)​

b) Alternative
Look like an ideal candidates for using Power Query.
In Power Query seems to be essentially

• Split string of Questions
• Unpivot split list
• Map to Chapters
• Pivot back
• Concatenate Chapters

 

[johnnyL]

Try this file:

Shortened version

It is about 1/3 the size of the original version.

If it works for you, just remember to hide/Password protect the sheets you desire.

 

[Michael M]

@johnnyL
"Paper2" still needs the excess rows / columns removed...might shrink it a bit more....great job though !!!

 

[johnnyL]

@Michael M I can't seem to get rid of those rows on that sheet, I think they are super glued on there.

 

[Fluff]

I can't seem to get rid of those rows on that sheet

Try changing the row height to (say) 21 & then save the workbook.

 

[johnnyL]

Try changing the row height to (say) 21 & then save the workbook.

That worked. Thank you @Fluff

 

[Fluff]

My pleasure.

 

[palaeontology]

Try this file:

Shortened version

It is about 1/3 the size of the original version.

If it works for you, just remember to hide/Password protect the sheets you desire.

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.

What did you do to reduce it's size, or as you said ... shorten it.

Kind regards,

Chris