extracting info related to maching entries

[Guest]

I have a table with various fields of information in each cell.
one column has a list of names, these are repeated randomly throughout the
column.
i want to creat a chart from information relating to each name. So I need to
search through the list and wherever the name entry equals for example, aaa
in column b, i need the related info for each maching name from column A.

So if i have:
1 aaa
2 bbb
3 ccc
4 aaa
5 aaa
6 ccc

I need to use values 1, 4 and 5 relating to aaa only to for example,
calculate an average for aaa.

 

[Guest]

for your example
=SUMIF(B1:B6,A11,A1:A6)/COUNTIF(B1:B6,A11)
a11 is any cell where you type your name you want the results for

 

[Bob Phillips]

Select B1:b20 and in the formula bar enter

=IF(ISERROR(SMALL(IF(A1:A20="aaa",ROW($A1:$A20),""),ROW($A1:$A20))),"",SMALL
(IF(A1:A20="aaa",ROW($A1:$A20),""),ROW($A1:$A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Ok that wasn't too difficult but now how would i be able to create a chart
for a specific name to show each related value.
I don't know of any way to do this directly in the charting function so
would i have to first create a new table with only the values relating to
each name?

like
1 aaa
4 aaa
5 aaa
to make the chart from these.

how do you create a new table like this from taking random distributed
entries in the origional table. its easy to extract the info to do a
calculation in one cell but how would i list the values in a new location?