Excel correlation of statistics

I have a spreadsheet (attached) that an expert has worked on to correlate statistical data.  I attach a stripped down version of the same here.

There is a lot of manual elements to this task which I would like to automate, if possible as follows:
2 separate sheets have to be copied in, renamed to Results & Scores
I then add 3 columns between Examiner & 'Station #, Question #, Response # Score' labelled 'Question asked', 'Assessor Number', 'Assessor Score'
I then paste the formula=INDEX(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("I")-64)),MATCH(1,(INDIRECT($A$1&"!A2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("A")-64))=M3)*(INDIRECT($A$1&"!C2:"&ADDRESS(MATCH(2^16,INDIRECT($A$1&"!$A:$A")),CODE("C")-64))=$C3),0),4+MOD(INT((COLUMN()-(CODE("N")-64))/4),6)) into every cell in the column 'Question asked'

Points to note:  there may be either 6 or 8 columns in the Results sheet.
Exam-Merged.xlsx
LVL 7
andymacfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
Hello Andy,
Before you run the procedure 'Transfer Scores' please take a minute to familiarise yourself with te environment in which the code runs. All the code is in the code sheet ScoreMan. Look at the top end. There are some items there which you can adjust. The code is constructed in such a way that you don't have to look elsewhere for knobs to twiddle.

At the very top you have two enumerations (Enums) for the two worksheets. You can modify any of these values so long as they reflect your actual worksheets. For example, NwsFirstData has a value of 4 now. If you were to insert an extra column before column D you could set this value to 5. The code will look for the first data in this column. Note that NwtFirstDataRow (Nwt is in the Target sheet) can't be smaller than 2.

Below the enums you have three constants which you can also modify.
    Const SourceSheetName As String = "Results"
    Const TargetSheetName As String = "Scores"
    Const FirstCaption As String = "Examiner"
These are the names of the two sheets and the word "Examiner" which is in the caption of the columns in the Target worksheet ("Scores") in which the examiner numbers are written. If you change those captions please change this constant to match.

The Worksheet Scores must be set up. The code just transfers the scores from the Results sheet and distributes them to the appropriate cells. In order to see a result you would need to clean out the scores already written or replace the Results sheet with another one.

Note that the Groups and Intervals (both of which we discussed in regard to your formula) are extracted automatically. The Group is derived from the number of column captions in the Results sheet. The Interval is counted from one "Question asked" in the Scores sheet to the next.

The program works from the Results sheet to the Scores sheet, which is the opposite direction the formula takes. The code picks up the candidate from Results and looks for a matching row in Scores. It then picks up the Examiner in Results and looks for that same number in the candidate's row alredy identified. It then dumps the results in the 4, 6 or 8 fields starting from that first one. The program doesn't check whether each of the cells have the correct examiner's number written next to it. Only the first one counts and all others are presumed.

In a more perfect world the program would create the entire sheet for you and write the examiner's name correctly itself. But that, as you have already surmised, is another question.  Perhaps, after you have this part going smoothly, you will turn toward it. When you do please consider getting rid of the entire sheet and replacing it with something that gives you the benefit you presently derive from this sheet in a more efficient way than this sheet every will be able to.
EXX-131016-Distribute-Exam-Score.xlsm
0
andymacfAuthor Commented:
Hi Faustulus

Thank you for this, I am hugely appreciative of your efforts.

If I can just clarify,  for the Enums you mention that NwsFirstData has a value of 4, where does this value come from as I cannot see it anywhere and therefore how would I change it.

Also, as we discovered yesterday, some of my sheets have 6 themes others have 8.  What do I need to adjust to allow for this as this will obviously have an effect on how many column dumps there are 4, 6 or 8?

Regards
Andy
0
andymacfAuthor Commented:
Hi Faustulus

I also have code that I use to add in the extra columns, would it be possible to include this, or something similar, in the script for the 'Scores' sheet
Sub insert_column_every_other()
LastColumn = ActiveSheet.UsedRange.Columns.Count
colx = 14
Do Until colx > LastColumn - 1
    Columns(colx).Insert Shift:=x1ToRight
    Columns(colx + 1).Insert Shift:=x1ToRight
    Columns(colx + 2).Insert Shift:=x1ToRight
    Cells(2, colx).Value = "Question asked"
    Cells(2, colx + 1).Value = "Assessor No"
    Cells(2, colx + 2).Value = "Assessor Score"
    colx = colx + 5
    LastColumn = ActiveSheet.UsedRange.Columns.Count
Loop
End Sub

Open in new window



regards
Andy
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

FaustulusCommented:
Hi Andy,
In my remarks at the very top of my code I gave some information about the nature of enums. Please read them. NwsFirstData deduces its value from the enumartion above it which must be 3 because that one deduces its value from the one above which must be 2 because that one follows an enum with the assigned value of 1.
Sorry, to have tested your attention in this way, but it's effective, isn't it?  :-)
I wrote
Note that the Groups and Intervals (both of which we discussed in regard to your formula) are extracted automatically. The Group is derived from the number of column captions in the Results sheet. The Interval is counted from one "Question asked" in the Scores sheet to the next.
You can change the groups from 6 to 8 or 16 just by having that number of captions in row 1 of the Results sheet.

Please, let's not go into extra columns at this moment. It is probably a small item as far as code is concerned but an item of huge importance in regard to work flow. I don't know in which sheet you wish to add columns, but I can definitely tell you that my code won't run with the new columns until you have amended the enums. Could you make this into another - follow-up - question which we can discuss in leisure after you have digested the new code I just gave you?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andymacfAuthor Commented:
Hi Faustulus

I understand now how it works, thank you for clearing that up. Sometimes at my age it takes a bit longer to sink in :)

I have tried this now with a live example and all is well, as expected.

I would be interested to hear your ideas for further development of this, and will raise an appropriate question as necessary.  From my perspective, i would like to automate it as much as I can as I want my colleagues to be able to run this process without much guidance.  I realise this may be a huge leap from where I am at the moment but I am just sharing my thoughts at this stage.

Thanks again
Andy
0
FaustulusCommented:
Hi Andy,
That's exactly my problem, too. I am looking for a venue to share thoughts without intruding upon your propriety. Basically, if you launch a question, asking to integrate the bit of code you posted above I would respond with this kind of argument:-
1. One mustn't keep the matches in the same drawer as the dynamite sticks. That code is designed to destroy what the other code needs. In addition to this, the code is a loose cannon because it can wreak its havoc on any sheet. So, I would argue to modify it to work on only one particular sheet and leave things with admonishing you to be careful where you apply it.
2. This is a tool designed to assist in the modification of a worksheet, presumably the Results sheet. But you shouldn't need any tools to modify that sheet because that entire sheet should be created by the same code that now only enters the some scores. I am thinking of a template having only the first 3 columns (or perhaps A:L), all the rest being created by the code, the requirements being dictated by the number of columns in the Source sheet. I feel inclined to ask you to post a question to that effect except that ...
3. I don't think that you need that sheet. For machine readability it isn't structured enough, and for letting the human eye wander over it it is too large. There is no monitor large enough to let you see it. For this reason I would rather discuss methods of getting rid of it than to create it mor efficiently.

For the latter point, the argument runs along the lines that the table is used for the purpose of either (a) looking at the scores of one student or (b) looking at the scores of one examiner or (c) looking at the scores relating to one question. The sheet doesn't do any of these things particularly well. That's where machine readability comes in. Code could be written to extract data from that sheet and display it in a dedicated form, to show the scores of one student, one examinar or one question.
Now, that is a marvellous idea, except that if you were to plan on such a system you wouldn't keep your data in a sheet like the one you have. The data format would be more like the Source data which, let's not entirely forget, was also produced from another worksheet which was too unwieldy to be read by machine.

That is the point where I see a silver lining. If you would have the data in a format of the Source sheet (which you already do) you can create the present worksheet from it, because you need that sheet to work with now, and you could use the same source data to create the other sheets I am suggesting. Eventually, the large sheet would just fall off the end of the workbench, because no one would want to use it any more if better tools are available to display the information it contains.

If you were to adopt this idea the focus of your work should be on the database, the Results sheet being one of the possible creations from it. True, it would be the most immediate but not the most important. With that in mind I would give my support to a project that aims at creating the Results sheet fully automatic. It will espouse the question of where data in columns A:L come from and possibly give them a new format. You could aim for having the sheet prepared more efficiently and walk away with improvements to your database into the bargain. From that point new targets will become visible.

Regards,
Faustulus
0
andymacfAuthor Commented:
Hi Faustulus

Thank you for your detailed comments, I can see you have put a lot of thought into this and I understand where you are coming from.

Currently, the format of the information I get is fixed and whilst I agree that it is unwieldy in this format, I'm afraid it comes from an old paradox database that I do not have any admin rights for.

However, hopefully there is light on the horizon in that I am currently trialing a new version of their software which has been migrated onto a SQL platform and I do have access to the background tables.  I am also working closely with a statistician and she has her own requirements about how data should be displayed and reported upon.  She is currently using the files that you have kindly manipulated and will feedback to me in due course.

So the crux of it is, that more requests may follow depending on how things pan out over the coming weeks.

Thanks again
Andy
0
FaustulusCommented:
Andy,
Thanks for telling me a little more. I think you can put up a question for creating the entire Results sheet. I was worried you might object to the questions I would have to ask, but as of now it seems to me like just a continuation of what we have been saying here.
Regards,
Faustulus
0
andymacfAuthor Commented:
Faustulus
Do you mean the whole workbook or just the 'Results' sheet, just so I can phrase my question correctly?

Andy
0
FaustulusCommented:
I mean the Scores sheet (sorry, some confusion here! Correcting my references from this point forward). Perhaps this "vision" will help:-
Columns A:l contain personal data - almost, that is. Perhaps you can lean on those who have the power to sort the columns into (a) personal data and (b) examination data. It is columns F:G which are different. They should be at one side of the set. But for present purposes A:L are one block. You should have a template somewhere where these data are stored and maintained. When creating the Scores sheet a reference to the workbook or worksheet must be provided. I think it should be another workbook, but if it is a sheet for now it can be changed into an external sheet easily later.
2. M:Q are repetitive column sets. The process of creating them will be to create one set and copy it x number of times. To create one set the code will contain the captions and the cell formats for each of the 5 columns (column width, too, if you like). Later, if you require more or fewer columns or another format the modification will be minimal and easy. Your wish list will be useful or, simply, one column set formatted the way you want.
3. We will need a list of examiners associated with each candidate. This information is contained in the Results sheet. In the workbook I last published rows 2:7 all refer to candidate 1, totally 6 rows which will mean that there will be 6 sets of columns. 8 consisting of 8 theme results. The number 8 is already taken from the number of columns in the Results sheet, so, now the number of examiners per candidate will also come from there.

When you ask your question please provide the workbook (or sheet) with the personal data (data removed, of course) and one set of columns M:Q and ask for a sheet to be set up based on the quantity of column sets required as per Results sheet. Post a copy of the most recent workbook and explain that the Results Scores sheet is a sample of what should be accomplished fully automatic.

This would include inserting the scores in the appropriate columns. This part of the code already exists. Of course, once the sheet is created all columns can be filled if the data are provided. For now we don't go beyond columns M and N (Examiner and Question). If you need to fill Station 3 as well I suggest to leave that for the next question. As for functionality, the sheet created will not be different from what you have now, only better controlled and column count automated. Whatever you can paste into your sheet now you will still be able to paste into it in future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.