Analysing data and creating goups in another sheet based on initial data. Also listing with no spaces between rows

[Rameses]

Hi all

Reposting as new post following advice

I am trying to analyse Reading/Writing and Maths point scores for specific students so that I can set up targetted intervention classes.

I want to look at the students points scores and place them (named student) into columns aka groups on another sheet that corresponds with their scores

Basically I want to analyse and sort the following data (from Sheet Y6)
Reading > Column K
Writing > Column M
Maths > Column O

and place the students first name (B) and surname (C) into its appropriate columns aka groups on Sheet Y6 Age App. I am sorting it by Row 3 and Row 4 on Sheet Y6 Age App. (please also advise on row 3 corrent name when it is for all 3 ie R/W/M if poss.)

As icing on the cake it would be excellent if the formula could also look at the Gender of each student > Column G on Sheet Y6 and colour their name everytime they are noted on Sheet Y6 Age App. ie. Blue for Boys and Pink for Girls

I have pasted a copy of what the final version should look like (Sheet Y6 Age App.) below ideally there should be no spaces between rows

Ideally I need worksheet formulas rather than an array or <ACRONYM style="BORDER-BOTTOM: rgb(0,0,0) 1px dotted; BORDER-RIGHT-WIDTH: 0px; FONT-VARIANT: normal; BORDER-TOP-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px; CURSOR: help" title="visual basic for applications">VBA as I wish to replicate formula in thw school data management systems called SIMS and run an analysis report every 6 weeks after reassessment.

Y6 Age App. aka what finished product should look like is below. (under that is the sheet Y6.

1+ Sublevel EXCEED

1+ Sublevel EXCEED

MET / EXCEED

ONLY MET

MET

NOT MET

1 Sublevel LESS

2 Sublevel LESS

3+ Sublevel LESS

Reading & Writing & Maths

Reading

Writing

Maths

Reading & Writing & Maths

Reading & Writing & Maths

Reading

Writing

Maths

Reading & Writing & Maths

Reading

Writing

Maths

Reading

Writing

Maths

Reading

Writing

Maths

>=29

>=29

>=29

>=29

>=27

>=27 & <=28

>=27 & <=28

>=27 & <=28

>=27 & <=28

<=26

>=25 & <=26

>=25 & <=26

>=25 & <=26

>=23 & <=24

>=23 & <=24

>=23 & <=24

<=22

<=22

<=22

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

First Name

Surname

Adriana

Calif

Adriana

Calif

Adriana

Calif

Tom

Side

Tom

Side

Adriana

Calif

Tom

Side

Billy

Bunton

Sid

Cath

Sid

Cath

Sabrina

Morgan

Londre

Vasquez

Billy

Bunton

Billy

Bunton

Billy

Bunton

Sabrina

Morgan

Ellie

Fooie

Ellie

Fooie

Tom

Side

Sid

Cath

Kate

Pill

Kate

Pill

Sabrina

Morgan

Kate

Pill

Ellie

Fooie

Ellie

Fooie

Bob

Butcher

Kate

Pill

Londre

Vasquez

Bob

Butcher

Bob

Butcher

Londre

Vasquez

<COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 4pt; mso-width-source: userset; mso-width-alt: 182" width=5><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 4pt; mso-width-source: userset; mso-width-alt: 182" width=5><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 3pt; mso-width-source: userset; mso-width-alt: 146" width=4><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 3pt; mso-width-source: userset; mso-width-alt: 146" width=4><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 4pt; mso-width-source: userset; mso-width-alt: 182" width=5><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 5pt; mso-width-source: userset; mso-width-alt: 219" width=6><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 5pt; mso-width-source: userset; mso-width-alt: 256" width=7><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1206" width=33><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 5pt; mso-width-source: userset; mso-width-alt: 256" width=7><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><TBODY>
</TBODY>

</ACRONYM>Many thanks for any help and advice on this

							SATS TEST RESULTS 2012
First name	Surname	SEN	sum born	Ethnicity	Gender	FSM	Spk/lst	Reading	Writing	Maths	Science
									POINTS		POINTS		POINTS
Adriana	Calif		sum born	WBRI	G			5	33	4	27	5	33
Billy	Bunton	SA		WBRI	B			N	0	3	21	3	21
Tom	Side	SA		WBRI	B			4	27	4	27	4	27
Sid	Cath	SA		WBRI	B			4	23	4	25	4	27
Sabrina	Morgan	SA		WBRI	G	Y		5	21	5	23	4	33
Kate	Pill	SA		WBRI	G			4	21	4	23	4	19
Ellie	Fooie	SA		WBRI	G			4	27	4	27	4	27
Bob	Butcher	SA	sum born	WBRI	B	Y		4	19	3	23	4	27
Londre	Vasquez	AP	sum born	WIRI	B			3	21	4	27	3	23

<COLGROUP><COL style="WIDTH: 47pt" span=15 width=62><TBODY>
</TBODY>

Rameses

 

[Sal Paradise]

Maybe I'm poor at understanding the request, but to me two things are causing an issue:

A) I have no idea what your "raw data" is that you will put in to a report
B) I have no idea what format you want your report in (the first table? The second?)

 

[Rameses]

Hi Sal thansk for getting back to me

1) Everything in the 2nd excel table is my raw data that I wish to be used to generate the info for the 1st excel table
2) Both tables are excel tables
Rows 1:5 in the 1st excel table are static
Choosing Adriana calif as the example we can see she scored on Sheet "Y6":
Reading 33
Writing 27
maths 33

Therefore I would like her placed in columns (of the first table) the Sheet is called "Y6 Age App."
D & E
H & I
K & L
R & S


The first table (Sheet "Y6 Age App." is what I want the final version to look like) it is a sorting exercise based on their point scores
hope this makes sense

please get back to me if it doesnt

regards

rameses

Maybe I'm poor at understanding the request, but to me two things are causing an issue:

A) I have no idea what your "raw data" is that you will put in to a report
B) I have no idea what format you want your report in (the first table? The second?)

 

[Sal Paradise]

Call me odd, but the format you want the data to end up in looks incredibly difficult to manage. While I am sure it can be done, you're going to be looking at a lot of different Array formulas which will take forever.

Given your data:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								SATS TEST RESULTS 2012
2	First name	Surname	SEN	sum born	Ethnicity	Gender	FSM	Spk/lst	Reading		Writing		Maths		Science
3										POINTS		POINTS		POINTS
4	Adriana	Calif		sum born	WBRI	G			5	33	4	27	5	33
5	Billy	Bunton	SA		WBRI	B			N	0	3	21	3	21
6	Tom	Side	SA		WBRI	B			4	27	4	27	4	27
7	Sid	Cath	SA		WBRI	B			4	23	4	25	4	27
8	Sabrina	Morgan	SA		WBRI	G	Y		5	21	5	23	4	33
9	Kate	Pill	SA		WBRI	G			4	21	4	23	4	19
10	Ellie	Fooie	SA		WBRI	G			4	27	4	27	4	27
11	Bob	Butcher	SA	sum born	WBRI	B	Y		4	19	3	23	4	27
12	Londre	Vasquez	AP	sum born	WIRI	B			3	21	4	27	3	23
Sheet2

This is the sort of formula it would be (and this is the simple one for one category -- it's this times three for the complex ones):

Excel 2010
	A	B
17	1+ Sublevel Exceed
18	Reading
19	>=29
20	First Name	Surname
21	Adriana	Calif
22
23
24
25
26
Sheet2

Cell Formulas
Range	Formula
A21	{=IFERROR(INDEX($A$4:$A$12,SMALL(IF($J$4:$J$12>=29,ROW($J$4:$J$12)-ROW($J$4)),ROW(A1))),"")}
B21	{=IFERROR(INDEX($B$4:$B$12,SMALL(IF($J$4:$J$12>=29,ROW($J$4:$J$12)-ROW($J$4)),ROW(B1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I strongly recommend you either:

A) Format your initial data better
B) Use a simpler output format