Data Help Required

[scottymelloty]

Hi,

I have a sheet full of data ie in different columns with lots of the names repeated but they could be home or away

A Smith ---- 10/04/05 ---- HOME ---- 4
J Andrews ---- 10/05/05 ---- AWAY ---- 6
A Smith ---- 10/04/05 --- AWAY 7
and so on

i currently sort all this data using the auto filter and when i sort say A Smith and Home it give me his average on the top line ,

i was wondering how on another sheet i could just have A Smith and a Home and Away Colunmn and it automatically reads all A Smith data and puts it in the home or away column from the original data sheet, i know how to use vlookup but that only reads 1 line, how do i read many lines that contain A Smith and get it to give me the data i want,


can someone give me a pointer as to what forumla i should be using or give me an idea on the best way going about sorting my data out better,

thanks

[olasa]

I would probably use a Pivottable
You can learn all about Pivottables here: http://www.datapigtechnologies.com/ExcelMain.htm

The other option would be to use an Array formula. But it depends on the size of the sample, how often it change and a few other things...
Example: =SUMPRODUCT(("A Smith"=A1:A100)*("Date"=(B1:B100)*("Home"=C1:C100))
Here's some additional info: http://www.personal-computer-tutor.c...ayformulas.htm

Hope it helps
Ola Sandström

[scottymelloty]

thanks will have a go at these options.