Transpose Data of 4 rows to column

[me_melb]

Hi ,


I've trivia quiz spreadsheet which has everything (question and answers) dumped into column A.

I would like to transpose/move multiple choice to column C D E F

I need to move the answers which always starts with "a." if it is multiple choice.
The questions always have ">" in it
The sheet names are same as file name and not sheet1 as BigBas' code (link below)

There are some spreadsheets with exception, if you see "1950s Classic Movies Trivia Quiz-2" there are only few multiple choice questions not all.

I've found following code on below thread which might be something i'm looking for

Re: Move data in a column to a row

http://www.excelforum.com/showthread...=1#post2894904

A macro will be good as there are around 108 files.

Appreciate your help.

[Marcol]

Your Data is for some reason is sometimes split into two lines, this leads to avoidable complications.
Best that you correct this first.

I have put a possible formula solution in this workbook, based on the simpler of your two examples.
I hope it helps you towards a solution of your problem.

Try Sheet "Select a Question"

[me_melb]

Thanks Marcol ! it is big help.

I'll play with your formula. Are you able to convert this formula into macro?

I'll clean up the data manually first and run the macro.

Don't worry about creating the Select A Question sheet...via the Macro.

Again thanks a lot for your hard work. I really appreciate it

[me_melb]

Hi,

I'm trying to copy and paste marcol's formula after cleaning up the data but I'm getting

Value not available error

I'm ticking YES to both questions. (If I click No it still does not work)

The screenshots are attached. Do I need to paste it with some special Technic ?

[me_melb]

I'll appreciate if anyone can help with this copy - paste problem.

Thanks !

[watersev]

hi me_melb, VB option if I understood your requirement correctly. Run code "test" (it is the same for both files)

[me_melb]

Thanks a lot Watersev.

It exactly does what I want in the files you've supplied. But when I copy the macro to another file and try to run it.

I get

Subscript out of range

error.

I'm taking entire code from your file and pasting into Sheet1 of new file.

Am i doing something wrong?

Example file attached.

[jindon]

Try this

Please Login or Register  to view this content.

P.S
Above code is based on your original file, so it doesn't work in your latest file.

This shoud work on your latest file

Please Login or Register  to view this content.

[watersev]

hi me_melb, please check attachment. All the posted sample sheets have been moved to this posted workbook. The code processes all of them correctly

[me_melb]

hi me_melb, please check attachment. All the posted sample sheets have been moved to this posted workbook. The code processes all of them correctly

Thanks a lot Watersev. This worked on 44 files out of 100.

And rest did get same subscript error. Are you able to put some comments in your code so I can just modify it depending on the spreadsheet (as the data is different on all sheets).

I do not want to go and ask each time there is an error.

Edit: Added few sample files.

[jindon]

Please Login or Register  to view this content.

[watersev]

@me_melb

The problem is that database has errors which lead to errors of the script that has no appropriate handling procedures for such events because I do not know all of them. The error is the same but the cause of it is different so comments will not be of any help here.

The posted file contains all posted quiz sheets, the code processes them all without errors now.

[Marcol]

This template using only formulae should work for all of your samples, provided that you do a few simple checks on your imported data.

1/. Copy your source data to Sheet "Raw Data" A1.
If you are copying straight from the net don't copy the line Links to Free Trivia on the Net and any following rows.
If you already have the data just delete these lines

Note
1.1/. There is often an error in the preceding line
e.g.
Not A Best Picture Trivia Quiz-
10> Stagecoach - Stagecoach came out the same year as Gone With the Wind. Links to Free Trivia on the Net
Delete the red text.

1.2/. There are cases where the data is split into 2 lines, the formulae handle this.
However there are some errors in the source data
e.g.
Not A Best Picture Trivia Quiz-
Row 61:= 9> Coal Miner's Daughter - Sissy Spacek won best actress in Coal Miner's Daughter.
Row 62:= Daughter.
This will be concatenated, so you should Delete > Cells > Move Cells Up = Cell A62

2/. Copy Sheet "Cleaned & Transposed" and Paste Special > Values to your destination sheet.

3/. Clear Sheet "Raw Data" Column A.

[me_melb]

@me_melb

The problem is that database has errors which lead to errors of the script that has no appropriate handling procedures for such events because I do not know all of them. The error is the same but the cause of it is different so comments will not be of any help here.

The posted file contains all posted quiz sheets, the code processes them all without errors now.

WaterSev you are a legend. This code now worked on all files.

I can not thank you enough !

Jindon & Marcol your help and effort is also much appreciated.

I've added Rep for all of you