Transpose Multiple Rows into a Single Row and Randomize in the Same Macro

[bwalks]

Hi, new to writing Macros here so I was wondering if someone might be able to give me some tips on the following.

I am trying to get some LMS data where currently a single question takes up 4 rows and the correct answer is always in the first row for each question designated by a 1 in the CORRECT column transposed into a format where each question and its respective possible answers are in the same row and the position of the correct answer is randomized instead of always being in first or (A ) position of a multiple choice answer. At the moment the data looks like the following:

CARDID QUESTION TEXT ANSWER CORRECT
7952 Question 1 Text liquidity risk 1
7952 Question 1 Text credit risk 0
7952 Question 1 Text interest rate risk 0
7952 Question 1 Text reinvestment risk 0
7903 Question 2 Text convertible debentures 1
7903 Question 2 Text bankers acceptances 0
7903 Question 2 Text commercial paper 0
7903 Question 2 Text negotiable CD's 0

I need it structured like the following where the answer is designated as Correct by either having a TRUE or FALSE next to it in a single row for each questions

CARDID QUESTION TEXT Answer_1 Answer_1_Correct Answer_2 Answer_2_Correct Answer_3 Answer_3_Correct Answer_4 Answer_4_Correct

7952 Question 1 Text liquidity risk TRUE reinvestment risk FALSE interest rate risk FALSE credit risk FALSE
7903 Question 2 Text commercial paper FALSE negotiable CD's FALSE convertible debentures TRUE bankers acceptances FALSE

Any help or ideas would be greatly appreciated.

Thanks! Bob

 

[hiker95]

bwalks,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


We can not tell where your raw data is located, worksheet name(s), cells, rows, columns, and, we can not tell where the results should be, worksheet name(s), cells, rows, columns.


So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[bwalks]

Thanks, I am using Excel 2010 on Windows 7

<title>Input Spreadsheet</title>Example starting spreadsheet:

*	C	D	E	F
1	cardId	Question text	answer	correct
2	7952	If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?	liquidity risk	1
3	7952	If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?	credit risk	0
4	7952	If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?	interest rate risk	0
5	7952	If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?	reinvestment risk	0
6	7903	All of the following represent securities typically found in the portfolio of a money market mutual fund except	convertible debentures	1
7	7903	All of the following represent securities typically found in the portfolio of a money market mutual fund except	bankers acceptances	0
8	7903	All of the following represent securities typically found in the portfolio of a money market mutual fund except	commercial paper	0
9	7903	All of the following represent securities typically found in the portfolio of a money market mutual fund except	negotiable CD's	0

<tbody>
</tbody>

Example desired output spreadsheet:

*	A	B	C	D	E	F	G	H	I	J
1	CARDID	QUESTION_TEXT__C	MULTIPLE_ANSWER_1_CORRECT__C	MULTIPLE_ANSWER_1__C	MULTIPLE_ANSWER_2_CORRECT__C	MULTIPLE_ANSWER_2__C	MULTIPLE_ANSWER_3_CORRECT__C	MULTIPLE_ANSWER_3__C	MULTIPLE_ANSWER_4_CORRECT__C	MULTIPLE_ANSWER_4__C
2	7952	An investor purchases a corporate bond on the secondary market for more than the bond's par value. Therefore	FALSE	the investor's current yield will be the same as the nominal yield	FALSE	the investor's nominal yield will be lower than the stated rate of return	TRUE	the investor's yield to maturity will be lower than the current yield	FALSE	the investor's yield to maturity will be higher than the nominal yield
3	7903	The registration statement for an issue of securities has just been declared effective by the SEC. This means	TRUE	The SEC has reviewed the registration statement and is allowing sales to commence.	FALSE	The SEC has declared the issue an effective investment opportunity for non-institutional investors.	FALSE	The SEC has approved the securities.	FALSE	The SEC has declared the issue an effective investment opportunity for institutional investors.
4

<tbody>
</tbody>

 

[hiker95]

bwalks,

Thanks for the screenshots.

I can not see, or, understand, where the text information in the Example desired output spreadsheet: is coming from?????


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[bwalks]

Here are examples of the actual spreadsheet starting and resulting formats.

Starting Format: http://www.examzone.com/resource/StartingFormatExample

Resulting Format: http://www.examzone.com/resource/ResultingFormatExample

These are an exact match that I prepared manually so hopefully that makes it clearer

Thanks! Bob

 

[hiker95]

bwalks,

When I tried to open both files, I received the following message:

The file you are trying to open, 'name of file' , is in a different format than specified by the file expression. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?


Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[FDibbins]

cross-posted here...
Transpose Multiple Rows into a Single Row and Randomize in the Same Macro

 

[hiker95]

bwalks,

A message to forum cross posters

Please read this:
Excelguru Help Site - A message to forum cross posters

 

[bwalks]

Solved here, sorry about the cross post

Transpose Multiple Rows into a Single Row and Randomize in the Same Macro [SOLVED]