#1
|
|||
|
|||
Formatting imported data into a table with variable record sets
Hi guys,
Thank you for reading my problem I will try to keep the description brief - I receive an excel sheet with the results of training validation, the workbook is created automatically from a specialist scanning software to collated student responses to paper survey questionnaires. See attached example to paint the picture data sheet. The number of questions varies from week to week and course to course as do the number of students. The questionaire is broken in to three response area T, M, P for each question, each area has different responses. What I am trying to do is collate the text response to each question and display them in a table. Sample attached Output sheet. I have been able to count the number of responses to each question using formula, but have been unable to do this without changing the formulas for each time the number of questions changes. Not sure how to tackle this - with formula, VBA or macros? I am sure this is a simple problem for someone Thanks |
#2
|
||||
|
||||
See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Paul,
Thank you very much for your reply and sample solution, I have been able to to do this. What I have been unable to to do and your solution doesn't do, is address the problem that the next imported data sheet might have 6 questions, therefore the column selection will be incorrect for the response absolute reference. The Results sheet will return zero on all responses after the 1st three questions (which is correct for the current data sheet) but would be incorrect if the number of questions changed up or down. This is what I am try to achieve - a results sheet that doesnt change when the number of questions changes (I have had other ideas on how to do this, but they have been upset by the common BLANK that is returned in all three response areas) Thanks again Last edited by ash6540; 03-21-2014 at 01:01 PM. Reason: Badly worded |
#4
|
||||
|
||||
To handle different numbers of questions, try the attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Almost works
Thank you very much for your reply - it works ok, sorry about the delay to your prompt response.
The only trouble I am finding is the question format in the import sheet is not always consistant, this is due to the mixture of English and Arabic text. Sometimes the text flips back to front on the import sheet (as arabic is read right to left). So makes it difficult to use as an absolute reference to count on. I was novicely experimenting with Loops because another sheet in the same workbook contains the number of questions. My theory was to do a loop on the cell range in relation to the number of questions (calculate, copy and paste the data into the table) and do it 3 times (once for each response area). But I haven't been able to achieve that If you or anyone else has any futher suggestions on how to tackle this problem it will be greatfully received???. |
#6
|
||||
|
||||
I have no experience merging RTL text. However, I think you might have to ensure the RTL & LTR texts are in different data columns, and that they're output to a table whose cells are correspondingly formatted for RTL & LTR texts.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thanks for your assistance, I will continue to search for a solution
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Finding sub-sets of data in worksheets | Rudi | Excel | 3 | 01-28-2013 06:13 AM |
Match two sets of data and display specific data | lolly150 | Excel | 1 | 05-14-2012 10:33 PM |
avoid duplicete record and merge the record with the existed record | hemant.behere | Excel | 0 | 01-10-2012 02:53 AM |
Finding the complements between the two data sets | psenku | Excel | 2 | 08-17-2009 11:15 PM |
One bar chart for 3 sets of data | rclloyd | Excel | 1 | 02-03-2006 09:51 AM |