Consolidating data


Press Alt F11 to open the VBE Press Control R to view the Project Explorer Navigate to the project for your workbook and choose Insert - Module Paste this code in the code pane Best and Simple solution to follow: Select the range of the columns you want to be copied to single column Copy the range of cells (multiple columns) Open Notepad Paste the selected range of cells Press Ctrl H, replace \t by \n and click on replace all all the multiple columns fall under one single column now copy the same and paste in excel Simple and effective solution for those who dont want to waste time coding in VBA Here is how you do it with some simple Excel formulae, and no fancy VBA needed. Please see this example spreadsheet: https://docs.google.com/spreadsheet/ccc? key=0Au Sy DFZlc Rt Hd GJOSn Fw REot Rz Ff M28t WElp Z1Fa R2c&usp=sharing#gid=0 You didn't mention if you are using Excel 2003 or 2007, but you may run into an issue with the # of rows in Excel 2003 being capped at 65,536. Also, can I ask what your end goal is for your analysis?The R community is very active and can help address any and all questions you may encounter. placed into each cell of your second workbook will retrieve the appropriate cell from the source sheet. ----------------------------------------------------------------------- oliver de paula Eu estarei ausente do Escritorio até 22/11/2006. If the respondent has visited Miami, there is a 1 in q_1.1.



--jim -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jim Moffitt Sent: Thursday, November 16, 2006 PM To: [hidden email] Subject: Consolidating Data I have inherited a data file that will require me to consolidate some variables. I'm trying to consolidate the accommodation questions (q_4.1, q_4.2, q_4.3, and q_4.4) to determine how many of the 16 total visits involved each type of accommodation. I'm trying to consolidate the duration questions (q_3.1, q_3.2, q_3.3, and q_3.4) to determine how many of the 16 total visits involved each duration category. Some respondents didn't as exemplified by the fact that case 109 has a system missing value for q_3.1 (Miami/duration). DATA LIST LIST (",") / id q_1.1 q_1.2 q_1.3 q_1.4 q_2.1 q_3.1 q_4.1 q_2.2 q_3.2 q_4.2 q_2.3 q_3.3 q_4.3 q_2.4 q_3.4 q_4.4. Pete q_1.3 Hilton Head q_1.4 Hatteras q_2.1 How did you get there?

There is a missing value issue that involves the fact that anyone who visited a city should have answered all three questions about that city.

If they don't, I think I want to insert a user-defined missing value of -99. I'm also unsure how to consolidate the transportation, duration and accommodation questions.