Microsoft Office Forums Formatting imported data into a table with variable record  sets

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-19-2014, 01:59 AM
ash6540 ash6540 is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Novice
Formatting imported data into a table with variable record  sets
 
Join Date: Mar 2014
Location: Abu Dhabi
Posts: 4
ash6540 is on a distinguished road
Default 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
Attached Files
File Type: xlsx ash6540 -Sample of Problem.xlsx (13.3 KB, 4 views)
Reply With Quote
  #2  
Old 03-21-2014, 02:16 AM
macropod's Avatar
macropod macropod is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

See attached.
Attached Files
File Type: xlsx ash6540 -Solution to Problem.xlsx (13.9 KB, 6 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 03-21-2014, 12:54 PM
ash6540 ash6540 is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Novice
Formatting imported data into a table with variable record  sets
 
Join Date: Mar 2014
Location: Abu Dhabi
Posts: 4
ash6540 is on a distinguished road
Question

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
Reply With Quote
  #4  
Old 03-21-2014, 03:25 PM
macropod's Avatar
macropod macropod is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

To handle different numbers of questions, try the attached.
Attached Files
File Type: xlsx ash6540 -Solution to Problem.xlsx (13.9 KB, 3 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-26-2014, 04:48 AM
ash6540 ash6540 is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Novice
Formatting imported data into a table with variable record  sets
 
Join Date: Mar 2014
Location: Abu Dhabi
Posts: 4
ash6540 is on a distinguished road
Default 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???.
Reply With Quote
  #6  
Old 03-26-2014, 06:05 AM
macropod's Avatar
macropod macropod is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-27-2014, 04:13 AM
ash6540 ash6540 is offline Formatting imported data into a table with variable record  sets Windows 7 32bit Formatting imported data into a table with variable record  sets Office 2010 32bit
Novice
Formatting imported data into a table with variable record  sets
 
Join Date: Mar 2014
Location: Abu Dhabi
Posts: 4
ash6540 is on a distinguished road
Default

Thanks for your assistance, I will continue to search for a solution
Reply With Quote
Reply

Thread Tools
Display Modes


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
Formatting imported data into a table with variable record  sets Finding the complements between the two data sets psenku Excel 2 08-17-2009 11:15 PM
Formatting imported data into a table with variable record  sets One bar chart for 3 sets of data rclloyd Excel 1 02-03-2006 09:51 AM


All times are GMT -7. The time now is 10:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft