HOW TO EXTRACT DATA FROM A TABLE

BAS872

New Member
Joined
Sep 17, 2002
Messages
5
I have 14 kids in a classroom and I am building a table to sort test scores and other data for each one of them. I have tried several combinations of FindInOrder to do what I describe below and none have worked:

-- Of the three children whose number (from 1 to 14) appears in cells D12:F12,

-- Place in cell D13 the the highest test X score from table DQ3:EW16 where:

-- DQ3:DQ16 lists the student's number (from 1 to 14 in ascending order) and


-- ED3:ED16 lists the score each student obtained in the test X.

I need to do that for THREE OTHER tests whose scores are in ED3:ED16, EL3:EL16 AND EL3:EL16

Could anyone give me a formula that will accomplish that? I would really appreciate it.

Thanks
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
Bas,

Your question is very confusing but I've put a few thoughts below which may help you.

Firstly, your table in DQ3:EW16 which starts with the student name. I would add a column to this which is =Max(DR3:EW3). This will give you the highest test score for each student.

Secondly, use a lookup function in cells D13,E13 and F13 to give you the best 3 score for each student.

=VLOOKUP(D12,StudentDetails,33,TRUE)
=VLOOKUP(E12,StudentDetails,33,TRUE)
=VLOOKUP(F12,StudentDetails,33,TRUE)

You will notice I've defined the table name as a range name. 33 is the offset in the table to the nex column.

To get the results from a specific test for each of your students use the lookup once again but this time the offset will need changing to be that of the test column.

I hope this helps you.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
I think you need to look at how you have set up your data. If you list everthing in separate columns perhaps Studentnane;Test;Score you can then use a PivotTable and/or AutoFilter to extract your data.I can email an example if you want
This message was edited by royUK on 2002-10-01 06:10
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
This sort of thing??
Book1
ABCDEF
1Data
212345
3testa1030807090
4testb5030656565
5
6
7student134
8Maxa80
9Maxb65
10
Sheet2




i.e.
=MAX(HLOOKUP(B7,B2:F4,2,0),HLOOKUP(C7,B2:F4,2,0),HLOOKUP(D7,B2:F4,2,0))

just using straightforward hlookups to get the relevant values & sticking the results in a max? Sure it could be shortened / arrayed, but it's late & I refuse toi think anymore.

CHange the references to suit...

Paddy
 

Forum statistics

Threads
1,144,148
Messages
5,722,792
Members
422,458
Latest member
Muirzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top