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

bwalks

New Member
Joined
Sep 6, 2014
Messages
4
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
Thanks, I am using Excel 2010 on Windows 7

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

*CDEF
1cardIdQuestion textanswercorrect
27952If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?liquidity risk1
37952If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?credit risk0
47952If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?interest rate risk0
57952If an investor inquires about the purchase of a bond mutual fund, *you would least likely identify which of the following risks?reinvestment risk0
67903All of the following represent securities typically found in the portfolio of a money market mutual fund exceptconvertible debentures1
77903All of the following represent securities typically found in the portfolio of a money market mutual fund exceptbankers acceptances0
87903All of the following represent securities typically found in the portfolio of a money market mutual fund exceptcommercial paper0
97903All of the following represent securities typically found in the portfolio of a money market mutual fund exceptnegotiable CD's0

<tbody>
</tbody>

Example desired output spreadsheet:



*ABCDEFGHIJ
1CARDIDQUESTION_TEXT__CMULTIPLE_ANSWER_1_CORRECT__CMULTIPLE_ANSWER_1__CMULTIPLE_ANSWER_2_CORRECT__CMULTIPLE_ANSWER_2__CMULTIPLE_ANSWER_3_CORRECT__CMULTIPLE_ANSWER_3__CMULTIPLE_ANSWER_4_CORRECT__CMULTIPLE_ANSWER_4__C
27952An investor purchases a corporate bond on the secondary market for more than the bond's par value. ThereforeFALSEthe investor's current yield will be the same as the nominal yieldFALSEthe investor's nominal yield will be lower than the stated rate of returnTRUEthe investor's yield to maturity will be lower than the current yieldFALSEthe investor's yield to maturity will be higher than the nominal yield
37903The registration statement for an issue of securities has just been declared effective by the SEC. This meansTRUEThe SEC has reviewed the registration statement and is allowing sales to commence.FALSEThe SEC has declared the issue an effective investment opportunity for non-institutional investors.FALSEThe SEC has approved the securities.FALSEThe SEC has declared the issue an effective investment opportunity for institutional investors.
4

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top