HELP!! Comparing a students starting point to their progress made and plotting in a chart?

I want a table in a sheet to look at data in another sheet that shows whether a group of students are on target or not for their expected grades. It should count and place the correct amount of students in the corresponding column (see below). It needs to be able to only look at filtered data and if it could also show the names of the pupils that correspond to that number if you hover over that would be amazing!! an example of the table is below!

Please help!!!! thanks Dan#


U G F E D C B A A*
T
B 1
N 1
2c
2b 1 1
2a
3c 1 5 1
3b 1 2 4
3a 1 6 9
4c 6 15 1
4b 11 24 5 2
4a 15 7 1 1
5c 7 16 6 1
5b 3 16 1 1
5a 5 1 2

Can you upload excel file and desired output if possible

Hi, thanks for your reply. If you can do this you are amazing!!

Ive attached a file with two sheets. Sheet 2 is the view I want to have and if you hover over the names (currently numbers) appear from the filtered list ( the next thing I need to work out how to do!)

Sheet 1 is the data I want to use to create sheet 2!

thanks

Dan

Hi jeza58

I am sorry but I couldnt understand full logic of your table thus cannot build a formula for it. For example. in your result table you inserted notes as a numbers which i think represent student names. But those numers are not in list. also does ethnicity play a role in expected result.

Hi eisayew,

I got the attachment I sent you from someone else but they locked the formulas so I cant work out how to rebuild it, but I cant really send the original as they may not be too happy! the notes should relate to the list in sheet 1 but theres something wrong with the formulas which is why I'm trying to rebuild it myself. For a bit of context If I use no 1 on sheet 1 for an example. Their KS2 Maths sub level is a 4a. this means they should be achieving a C so on sheet 2 in row 15, column h there would be a 1. If another 3 4a students were on a C grade the number in that cell would be a 4 and the notes have each of their names (numbers at present!). If this person was achieving a D, they would be counted in column G instead.

The ethnicity, SEN, gender etc is needed for filtering purposes but in terms what you are trying to help me with at the moment they arent required! So from sheet 1 the only columns you need to look at really are A, G and H. If possible J and K as well to create the same table but for English below the first one!

thanks for trying, very much appreciated!

thanks
Dan