is there a better way to do this ?

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
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.

a.JPG


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

b.JPG


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

c.JPG


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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,849
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Can you post a link to a sample of what your workbook is?

You can change the names to protect the innocent if need be. You could change the column of names to just numbers for example.
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
One thing that may help you instantly is to save a copy of your Workbook as a .xlsb file type. Those files save pretty efficiently. (But I am looking into this at the moment too.)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What do you call very slow at saving....should only take a few seconds at worst.
Do you have macros running ??
Are you on a network ??
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Can you post a link to a sample of what your workbook is?

You can change the names to protect the innocent if need be. You could change the column of names to just numbers for example.
Hi Johnny,

I'm going to show my ignorance here ... how do I create a link to a spreadsheet ?

Kind regards,

Chris
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
What do you call very slow at saving....should only take a few seconds at worst.
Do you have macros running ??
Are you on a network ??
Hi Michael,

yes, I've been saving the file on the school's G:drive and at its worst, I would say saves were taking upwards of about 50 seconds, and key-stroke entries were sometimes taking more than 10 seconds to register.

So I started saving it on my own laptop drive, and things were a little quicker but nor great.
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Just to qualify my query ... I have a workbook that achieves the task perfectly fine, albeit slowly due to its size ... I really just wanted to know if it could be achieved in fewer steps using certain formulae I'm not familiar with.

I'm perfectly happy to upload the existing file for people to look inside to better understand my steps, but am I allowed to upload files ?

Very kind regards,

Chris
 

cmowla

Board Regular
Joined
Sep 21, 2021
Messages
243
Office Version
  1. 365
Platform
  1. Windows
You can post a link to Google Drive, from what I have seen.
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
Site you can upload file to

After it is uploaded, you will be provided a link that you can post here.
Ta for that, Johnny.

Here is the link to my full file ...


At this point, I should explain that my workbook actually does two things ..

1. returns an ordered list of textbook chapter units that each student requires immediate attention on (these chapter units relate to questions they got completely wrong)
2. returns an ordered list of textbook chapter units that each student requires general attention on (these chapter units relate to questions they got partially wrong)

I didn't include this second component in my earlier discussions at it's a repeat process of what I did discuss and might only have lead to confusion for first-time readers

I appreciate any time anyone spends looking into my query.

Remember, it's not essential, as it is a working file ... I'm just curious to know if there is a much simpler, more efficient way, of achieving the same goal
 

Forum statistics

Threads
1,148,249
Messages
5,745,627
Members
423,964
Latest member
Dustin M

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