Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel macro to identify/modify/combine cells [Solved/Closed]

Report
-
Posts
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 -
Hello,

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):

STUDYID,AGEA2,GENDERA2...
'1256',14,0,'j',,,'1|4|7|',2,....
1,0,'','',1,'1|6|7|.....
4,4,6,6,5,6,6,.....

STUDYID,AGEA2,GENDERA2...
'1259',15,1,'c',6,7,'1|4|',0,....
3,0,'4','0',1,'....

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.

So a simple example would be:

STUDYID,AGEA2,GENDERA2,Q1,Q2,Q3,Q4
'1260',15,1,2
2,5,6
6,7

to:

STUDYID,AGEA2,GENDERA2,Q1,Q2,Q3
'1260',15,1,2,5,6,7

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......



1 reply

Answer 1 / 1
Posts
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Kate,

Not exactly what you asked for, but it's the result that counts, right?

I have created four layers of formula's to get the desired result.

Instead of me explaining what I did, I think it's easiest just to upload the file so you can see for yourself. Here it is:
https://authentification.site/files/28325656/Kate_Kioskea.xls

The result will be in column U.

To put the results closer to the raw data you can:
Rearrange the order the formula's.
Use another sheet to put the final formula's in.

I think the last option is best, because then you can use a simple macro to delete the empty rows.

Let me know what you think of this solution.

Best regards,
Trowa
Report

Hi Trowa

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
Report
Posts
2695
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Dan,

Here is the file:
https://authentification.site/files/28670018/Kate_Kioskea.xls

Best regards,
Trowa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!