Excel Question

[Joanne]

OK I've got to upload all of our children onto Accelerated Reading.

 

SMT want as much data as possible on there so we can see progress of SEN children and FSM children etc.

 

AR have a weird format that you have to adhere to for these extra nuggets of information. So my question is this:

 

I want excel to check 4 or 5 columns for a value (eg T or F) and if T add a code.

 

eg/ FSM - T, SEN - T, GT - F, Scharacteristics - "FM-SE"

 

I'm guessing it's gonna be a long line of basic, but it could save me going through each child on excel and potentially making mistakes.

 

Thanks in advance.

[Steve21]

If you want a quick way, just select the column and do a find and replace. It'll only affect that column then.

 

So like select FSM column, do find T replace with FSM - T etc

 

Or isn't that laid out right on your end that way?

 

Steve

[Joanne]

I need all of the columns to be looked at and then fill out a single column with a code of what is in the others....

 

Find and replace will help me with the language codes though! Thanks

[Joanne]

These are my instructions...

 

SCHARACTERISTIC Student Characteristics 1000 "Dash (-) separated character string

 

For example:

ADA-GT" DY Dyslexic

EAL English as an Additional Language

FM Free School Meals

GT Gifted and Talented

LDIF Learning Difficulties

PD Physical Disabilities

SE Special Education Needs

 

I have a report from SIMS with all this info, but I need to condense it into one column with the codes above.

[andy_b]

Quick thoughts were to concatenate some IF statements together: =IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT","") ?

 

Maybe a bit simple but seemed to work.

[Steve21]

Quick thoughts were to concatenate some IF statements together: =IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT","") ?

 

Maybe a bit simple but seemed to work.

 

Aye was thinking same, but only downside is if you start getting some true and some false you could end up with dashes in wrong place. E.g. In your example if FM was false you could get -SE-GT, You'd have to split the dashes up into their own if statements based on the last box etc

 

Steve

[pcstru]

I'd slap another tab into the workbook and use that for some lookup tables, so the attributes map to the short AR code or if they are false, to an empty cell (or if the AR code is looking for position, mat to a space to pad). You could possibly do this all in one table - no need for separate tables for SEN - T and FSM - T. I'd then use the table in a VLookup and then concatenate the mapped values for the final column. I'd then cut and paste "values", remove unwanted columns and finally spit out a CSV.

[sted]

any chance of posting a row or 2 from the table and what you want out the other end im struggling to work it out

[andy_b]

Aye was thinking same, but only downside is if you start getting some true and some false you could end up with dashes in wrong place. E.g. In your example if FM was false you could get -SE-GT, You'd have to split the dashes up into their own if statements based on the last box etc

 

Steve

 

=IF(LEFT(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""),1)="-",RIGHT(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""),LEN(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""))-1),IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""))

 

Simples

 

(Although 7 possible options would make it rather long )

Edited November 8, 2017 by andy_b

[Joanne]

EG -

 

So in their SCharacteristics Cell would be:

1: EAL

2:

3: FM-PP

4: EAL

[Joanne]

=IF(LEFT(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""),1)="-",RIGHT(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""),LEN(IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""))-1),IF(A2="T","FM","")&IF(B2="T","-SE","")&IF(C2="T","-GT",""))

 

Simples

 

(Although 7 possible options would make it rather long )

 

That's almost working... just need to add in the EAL one too.... but I've got an idea of what to do!

[Joanne]

OK that's great, thanks guys!

 

In the end I used:

 

=IF(LEFT(IF(I2="T","FM","")&IF(K2="SEN Support","-SE","")&IF(L2="T","-GT","")&IF(M2="Yes","-EAL",""),1)="-",RIGHT(IF(I2="T","FM","")&IF(K2="T","-SE","")&IF(L2="T","-GT","")&IF(M2="Yes","-EAL",""),LEN(IF(I2="T","FM","")&IF(K2="SEN Support","-SE","")&IF(L2="T","-GT","")&IF(M2="Yes","-EAL",""))-1),IF(I2="T","FM","")&IF(K2="SEN Support","-SE","")&IF(L2="T","-GT","")&IF(M2="Yes","-EAL",""))

[ADMaster]

Looks like you have it worked out and I'm late to the party but...

Can you extract data from sims with direct SQL queries?

I use powershell to extract the data directly from my MIS via SQL, do some logic on it and put it into the format AR want.

However, I do not use Scharacteristics as it is not a required field.

 

This may be a goal you can work toward to semi automate the process for you. Now if only you could automate the import without it costing an arm and a leg.

 

I had no idea anyone used AR over there, welcome to the fun and terrible imports.

[Joanne]

Accelerated Reading?

 

I'm not touching the SQL with any kinda barge pole! Hahahah!!

 

Just asking them about our book list, because it is coming up as 31,000 when actually it should be more like 5,000.