Excel 2007 : Organizing data from Tab delimited source

[Wizywyg]

Hi,

I need help in formatting a data dump of survey results from a DB into a readable format.

Currently the data I have looks like this:

Please Login or Register  to view this content.

Some questions were multiple-choice and those questions could have as many as 30 choices to choose from.

Basically I need to take these results and display them in a format like this (Q=question):

Please Login or Register  to view this content.

So if Q4 had say 5 multi-choice answers, the top would be Q4-1, Q4-2, Q4-3, Q4-4, Q4-5
And if Q10 had say 10 multi-choice answers, the top would be Q10-1, Q10-2 and so forth.

For those columns that have multi-choice, need to detect which columns were picked and signify by a 1=yes 0=no for being chosen by that user (or 0 if the user left it blank for all columns)

The survey had 40 questions that could have been answered by those participating. The survey ended, if they were directed from one question, to skip over others if they didn't meet the criteria to go onto the next one.


I've tried pivot tables, and that helped, but the table only listed all the questions by the User down a column instead of across the top, which is what I'm looking to do.

Help greatly appreciated.

[Wizywyg]

Hi,

Just got some dummy data to test with.

[pike]

hi Wizywyg,
is the source a txt file or xls ??

[pike]

hI Wizywyg
To organise the data after the import with different question number for question4 eg question4-1 question4-2 ...ect...

Please Login or Register  to view this content.

Can you change the question numbers?

[pike]

or just to have all the same question answers in the same cell

Please Login or Register  to view this content.

[Wizywyg]

Hi thanks but where do i put that code?

[pike]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[pike]

Hi Wizywyg

Thinking that the easiest way to get the desired question numbering layout
is if we know how many question will be asked before we start as sorting will fail - sequence will be 4-1 , 4-10 ,4-11,4-12, 4-2 not 4-1 ,4-2 ...

or have all the question event if they not answered - blank

Please Login or Register  to view this content.

what contorl do you have in the import or structure of the data?