Extracting names from a database.

  • Thread starter Thread starter Campbell
  • Start date Start date
C

Campbell

In column A I have twenty groups, in Column B ref#, In Column C First
Name, In Column D Surname and In Column E the numerical value.
The data rows are from 2 to 534.
I can find the total and maximum for each group using arrays.
1. What I like is the name of who has the highest value in each group.
2. If they are tied then all the names that are tied maximum.
3. If possible all the names in the group with a value over 2.

I hope that this makes sense.
 
Hi
try:
1. =INDEX(D2:D534,MATCH(MAX(E2:E534),E2:E534,0))

2. Try the following array formula:
=INDEX($D$2:$D$534,SMALL(IF($E$2:$E$534=MAX(E2:E534),ROW($E$2:$E$534)),
ROW(1:1)))
and copy this down

3. Use a Filter for this
 
Hi
try:
1. =INDEX(D2:D534,MATCH(MAX(E2:E534),E2:E534,0))

2. Try the following array formula:
=INDEX($D$2:$D$534,SMALL(IF($E$2:$E$534=MAX(E2:E534),ROW($E$2:$E$534)),
ROW(1:1)))
and copy this down

3. Use a Filter for this
Click to expand...

Frank thanks for your response, what this gives is name top for the
whole range. However I have 20 different names in column A which are
sorted alphabetically.
e.g one of the groups called NJK in column A which person has the
highest value. I have a helper column G with all 20 names in G2:G21
 
Back
Top