Is there a way to mass replace all?

[mikeyates]

First of all, you all have been great. I really appreciate this forum.

We have a 27 question survey that I have to report. We can have up to 45 students. 23 of those 27 questions have 6 options and the last 4 questions are comments.
I have to report this in XML later, but I don't think matters for my question.

I have found a way (thanks to earlier help) to automatically take the evaluations and put them in the report format that I need.
However, I'm trying to fill that on down to the possible 45 students.

Excel wants to assume the next wrong. It wants to go chronologically, and my report doesn't work that way.
Right now, I'm having to remove the =, find and replace all one line at a time. But I'm expecting 1300 lines.

Can I customize the way Excel auto fills?
Can I write a script for a mass replace all?

Just to clarify, Each student is one row, and each question is one column.
I report them one student at a time.

I took a screenshot of two students, one with the = and one without, so that you could see what it's supposed to look like, and what I'm dealing with behind the scenes.
You'll see the pattern of the rows and columns.

Forgive me if this question isn't really an excel question and is more of a bash script question.

 

[duggie33]

I took a screenshot of two students, one with the = and one without, so that you could see what it's supposed to look like, and what I'm dealing with behind the scenes.
You'll see the pattern of the rows and columns.

I cannot find what you are describing above. Can you show a screenshot of what it is and what you want it to be? Is everything in your screenshot in column A? It is hard to tell with the current formatting.

Thanks,

Doug

 

[mikeyates]

It's such a large report, horizontally, that I can't get the whole thing in a screenshot that you can read.
I can't get the mini sheet add in to work. I think my organization has something blocking it.
I created a smaller, simpler sheet, just one page. I put on my google drive, if you're willing to take a look. I understand if you're not.

Other than that, here are more screenshots of the one on my google drive.

Google Drive Link


This is the original report format. I download this from Blackboard (my LMS) and paste it to my report as Sheet1 (I actually name it something else). It goes all the way to column FL.

This is the output of one student. It's the culmination of row 3 from the previous screenshot.

This is the script I use to get the result in the previous screenshot. You can see the pattern of the rows and columns.
Excel messes up the auto numbering when I copy and paste because my questions are not in chronological order.
Again, There are 27 questions, and up to 45 students, so it is very tedious to go line by line changing the referenced cells.

 

[rlv01]

Just to clarify, can you explain why the normal "Replace All" option for the standard Excel 'Find/Replace' feature won't do what you want (i.e. replace all rows, all columns)?

 

[mikeyates]

Just to clarify, can you explain why the normal "Replace All" option for the standard Excel 'Find/Replace' feature won't do what you want (i.e. replace all rows, all columns)?

Because it only works one line at a time, and there are 1300 lines total. If you look at the screen shot with the scripts for example, the first row has cells G3. Only that row will ever reference G3. So, Replace All is only good for that one row. I'm trying to avoid using Replace All 1300 times. I don't mean that ugly or sarcastic. Forgive me if it sounded rude.

 

[Fluff]

Is the result you want

<evaldata>
<question id="1" answer="5/>
<question id="2" answer="4/>
<question id="3" answer="3/>
<question id="4" answer="3/>
<question id="5" answer="3/>
<question id="6" answer="3/>
<question id="7" answer="3/>
<question id="8" answer="4/>
<question id="9" answer="4/>
<question id="10" answer="4/>
<question id="11" answer="3/>
<question id="12" answer="3/>
<question id="13" answer="3/>
<question id="14" answer="3/>
<question id="15" answer="3/>
<question id="16" answer="3/>
<question id="17" answer="3/>
<question id="18" answer="3/>
<question id="19" answer="3/>
<question id="20" answer="3/>
<question id="21" answer="3/>
<question id="22" answer="3/>
<question id="23" answer="3/>
<question id="24" answer="the experience/>
<question id="25" answer="broken labs/>
<question id="26" answer="have coarse documentation in sync\updated w the labs..&nbsp;/>
<question id="27" answer="<Unanswered>/>
</evaldata>
<evaldata>
<question id="1" answer="3/>
<question id="2" answer="4/>
<question id="3" answer="3/>
<question id="4" answer="1/>
<question id="5" answer="1/>
<question id="6" answer="1/>
<question id="7" answer="1/>
<question id="8" answer="4/>
<question id="9" answer="4/>
<question id="10" answer="2/>
<question id="11" answer="3/>
<question id="12" answer="3/>
<question id="13" answer="3/>
<question id="14" answer="4/>
<question id="15" answer="2/>
<question id="16" answer="1/>
<question id="17" answer="1/>
<question id="18" answer="4/>
<question id="19" answer="2/>
<question id="20" answer="1/>
<question id="21" answer="2/>
<question id="22" answer="1/>
<question id="23" answer="1/>
<question id="24" answer="None/>
<question id="25" answer="Labs/>
<question id="26" answer="make presenter slides more in tune with course material provided. Labs inadequate and don’t work/>
<question id="27" answer="<Unanswered>/>
</evaldata>

 

[mikeyates]

Is the result you want

<evaldata>
<question id="1" answer="5/>
<question id="2" answer="4/>
<question id="3" answer="3/>
<question id="4" answer="3/>
<question id="5" answer="3/>
<question id="6" answer="3/>
<question id="7" answer="3/>
<question id="8" answer="4/>
<question id="9" answer="4/>
<question id="10" answer="4/>
<question id="11" answer="3/>
<question id="12" answer="3/>
<question id="13" answer="3/>
<question id="14" answer="3/>
<question id="15" answer="3/>
<question id="16" answer="3/>
<question id="17" answer="3/>
<question id="18" answer="3/>
<question id="19" answer="3/>
<question id="20" answer="3/>
<question id="21" answer="3/>
<question id="22" answer="3/>
<question id="23" answer="3/>
<question id="24" answer="the experience/>
<question id="25" answer="broken labs/>
<question id="26" answer="have coarse documentation in sync\updated w the labs..&nbsp;/>
<question id="27" answer="<Unanswered>/>
</evaldata>
<evaldata>
<question id="1" answer="3/>
<question id="2" answer="4/>
<question id="3" answer="3/>
<question id="4" answer="1/>
<question id="5" answer="1/>
<question id="6" answer="1/>
<question id="7" answer="1/>
<question id="8" answer="4/>
<question id="9" answer="4/>
<question id="10" answer="2/>
<question id="11" answer="3/>
<question id="12" answer="3/>
<question id="13" answer="3/>
<question id="14" answer="4/>
<question id="15" answer="2/>
<question id="16" answer="1/>
<question id="17" answer="1/>
<question id="18" answer="4/>
<question id="19" answer="2/>
<question id="20" answer="1/>
<question id="21" answer="2/>
<question id="22" answer="1/>
<question id="23" answer="1/>
<question id="24" answer="None/>
<question id="25" answer="Labs/>
<question id="26" answer="make presenter slides more in tune with course material provided. Labs inadequate and don’t work/>
<question id="27" answer="<Unanswered>/>
</evaldata>

It looks right. I can't get BB to work, so I can't see what you did, but the results look pretty. What did you do?

 

[Fluff]

The formula is

=LET(data,G3:FG29,TOCOL(HSTACK(EXPAND("<evaldata>",ROWS(data),,"<evaldata>"),"    <question id="&CHAR(34)&SEQUENCE(,27)&CHAR(34)&MAP(FILTER(data,LEFT(G2:FG2,6)="answer"),LAMBDA(a," answer="&CHAR(34)&IFERROR(LEFT(TEXTAFTER(a,"(")),a)&"/>")),EXPAND("</evaldata>",ROWS(data),,"</evaldata>"))))

I need to alter it to account for brackets in the last few questions, but if you check the rest before I do that.

 

[mikeyates]

The formula is

=LET(data,G3:FG29,TOCOL(HSTACK(EXPAND("<evaldata>",ROWS(data),,"<evaldata>"),"    <question id="&CHAR(34)&SEQUENCE(,27)&CHAR(34)&MAP(FILTER(data,LEFT(G2:FG2,6)="answer"),LAMBDA(a," answer="&CHAR(34)&IFERROR(LEFT(TEXTAFTER(a,"(")),a)&"/>")),EXPAND("</evaldata>",ROWS(data),,"</evaldata>"))))

I need to alter it to account for brackets in the last few questions, but if you check the rest before I do that.

So, I need to do that 45 times, once for each student, and change the range for the corresponding top left to bottom right?

I'm not complaining, as that's a huge improvement. I just want to make sure I understand.

 

[Fluff]

So, I need to do that 45 times, once for each student

No just once, all you need to change is the Range at the start to get all the rows.