I have a collection of survey results in Excel that are coded from another software that I need to prepare to put in SPSS, the results come back as comma delimited rows of data each hundreds of characters long. Normally I would just separate them into columns and then import to SPSS, but a glitch has results split into several rows for most of the results.
I am trying to figure out an excel macro that would allow me to fix the results not by hand, but need some help.
The results look something like this (times ~1300):
and they need to have all numbers per result in one single line to be properly split into columns and imported.
Sadly the biggest problem is that the glitch caused the creation of the new line to repeat a number. So if the first row ends on "4", the next row repeats that "4" then continues the rest of the data.
So I need to first identify the results that have been split, then I need to find the last row of results for that person (always split by the headers) and delete the first character only (not including the comma which has to stay to create the new column for that question), then cut that cell and append it to the end of the numbers in the cell of the row immediately above it. Sometimes the row has only been split into 2 rows, other times it has been split into up to 4.
Again where all of the information in each line is contained within one cell column A.
Other tidbits:
The study ID information is not always 4 digits, sometimes they are 5,
The location of the splits in the rows is not consistent
Any commas, single quotation marks etc. must stay as part of the coding of the results.
The study ID information are not in numerical order, nor is every number between the highest and lowest ID present.
There is a blank row between the end of one result and the headers for the next results.
I have worked with Macros a few times before, though it was awhile ago and I have used several other programing types and often get them confused. Here is what I have kind of thought of/put together, though I realize some of it may be unneeded/incorrect/missing /not code etc. I'm stuck on how to paste the modified row into the cell above it to combine them, not replace it. Also not sure how to account for results being split anywhere from 0 to 4 times without ridiculously crazy for and if loops.
Thanks for any and all help/suggestions!!
Kate
Sub CombineSplitRows()
Sheets("Sheet1").Select
Cell(1,"A").Select
For cell(x, "A") where x<1325
If Cell.Find /= "study" then
cell.offset(1,0).select
else if cell.Find = "study" then
cell.offest(2,0).select
if cell.value = "" then
cell.offset(1,0).select
else if cell.value/="" then
cell.value = mid (cell,1,999)
cell.value.copy
cell.offset(-1,0).value=cell.offset(-1,0).value & PASTE????
cell.clearcontents
end If
End if
Loop
Basically it needs to follow something like this:
Find row with "study" in it,
move down 2
is this row blank?
if yes move down one more and go back to top of loop
if no move down one more and determine if blank
if yes
move up one row
truncate first character
cut this cell's values
move up one row
paste to the right of the values in the cell
move down 3 rows and go back to top of loop
if no
move down 1 row, determine if it is blank
if yes
move up 1 row
truncate first character
cut this cell's values
move up one row
paste to the right of the values in the cell
truncate first character
cut this cell's values
move up one row
paste to the right of the values in the cell
move down 4 rows and go back to top of loop
if no......
I'm a colleague of Kate's. I clicked on the file and it said the file does not exist. I am really interested in seeing if the file worked, so if there's a way for me to get to it, that would be awesome.
I'm a colleague of Kate's. I clicked on the file and it said the file does not exist. I am really interested in seeing if the file worked, so if there's a way for me to get to it, that would be awesome.
Thanks
Dan
- Posts
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396Here is the file:
https://authentification.site/files/28670018/Kate_Kioskea.xls
Best regards,
Trowa