Duplicate Numbers when using the Large Function.

[jeel]

Page 1 info looks like this. I want page 2 to look like this.

Page 1 is named WKLY. Page 2 is named results.
A B C A B C
1 1st# 2nd# name 1st# 2nd# name
2 340 352 mike 362 432 bob
3 340 641 tom 358 516 joe

4 358 516 joe 340 641 mike
5 362 432 bob 340 352 tom

I am working in groups of 4.

I used the formula =LARGE(wkly!$A$2:$A$5,1) to move column a

info to page 2 but I also wanted column b and c. Max posted the following
formula which worked fine until I came to the duplicates.

=INDEX(wkly!a$2:a$5,MATCH(LARGE(wkly!$a$2:$a$5,ROWS($1:1)),wkly! $a$2:$a$5,0))
Max posted me another formula but I can't seem to make it work. Any help

would be appreciated. I hope this example makes sense. Also, in my first
formula the 1 on the end won't update to 2,3,4 as I drag it down. Would
it help
to add another number column or use a different formula to move column a?
Thanks, jeel.

 

[Max]

Just posted this reply below to your other thread in .misc
-------------------
This sample illustrates one way you can get it going:
http://www.freefilehosting.net/download/3b45k
Extract lines in descending order in another sht.xls

Source data in "wkly", data in cols A to C from row2 down. Lines to be
extracted in "Results", sorted in descending order by the "1st" col

In Results,
In A2: =IF(wkly!A2="","",wkly!A2-ROW()/10^10)
Leave A1 blank

In B2
=IF(ISERROR(LARGE($A:$A,ROWS($1:1))),"",INDEX(wkly!A:A,MATCH(LARGE($A:$A,ROWS($1:1)),$A:$A,0)))
Copy B2 to D2. Select A22, copy down to cover the max expected extent of
source data in "wkly". Minimize/hide away col A. Cols B to D will return the
results you seek.

---

 

[Teethless mama]

"First", "Second", and "name" are defined name ranges (no quotes)

In A2: =LARGE(First,ROWS($1:2))
copy down

In B2: =LARGE(IF(First=A2,Second),COUNTIF($A$2:A2,A2))
ctrl+shift+enter, not just enter
copy down

In C3: =INDEX(name,MATCH(1,(First=A2)*(Second=B2),0))
ctrl+shift+enter, not just enter
copy down

 

[Teethless mama]

correction should be:
A2: =LARGE(First,ROWS($1:1))

 

[jeel]

I can't seem to make the formula work. Colomn a returned the right answer.
Colomn B gave me 0, or num error. Column c gave me n/a error. Does it make
a difference if I'm using Vista and Excel 2007? Thank you. jeel

 

[jeel]

I got the formula to work in a2:c5, but if I want it anywhere else on the page
I can't seem to get it to undate correctly. I think I am in way over my head.
Does it make a difference if I'm using Vista and Excel 2007?
Thank you for your time and help. jeel

 

[Max]

Does it make a difference if I'm using Vista and Excel 2007?
Don't think so.

Do not change the ROWS($1:1) part in the top cell formula (in B2)
when you adapt to suit

 

[T. Valko]

Does it make a difference if I'm using Vista and Excel 2007?

In Excel 2007 you can use the new IFERROR function:

=IF(ISERROR(LARGE($A:$A,ROWS($1:1))),"",INDEX(wkly!A:A,MATCH(LARGE($A:$A,ROWS($1:1)),$A:$A,0)))

=IFERROR(INDEX(wkly!A:A,MATCH(LARGE($A:$A,ROWS($1:1)),$A:$A,0)),"")

There are still some situations where a "crafted error trap" is more
efficient than IFERROR.

 

[jeel]

I tried to upload a sample file at senduit.com. http://senduit.com/65c65f
I can't figure out how to make it a link for you. Maybe you can access it
with the address. File name is jeel.xlsx. Thank you.

 

[jeel]

I tried posting a file as you suggested. I posted a file on
http://senduit.com. file name jeel.xlsx.
http://www.freefilehosting.net/download/3b72i
This is the first time I have done this so I'm not sure it will work.
Thanks, jeel

 

[Max]

Here's the link to your sample
with the solution to your main problem1 implemented:
http://www.freefilehosting.net/download/3b817
jeel_971_1.xls

In Wkly,
Insert a new col I to act as the tiebreaker for the averages in col H
In I2: =IF(H2="","",H2-ROW()/10^10)
Copy down to I5

Then in 1, point the index/match formulas at the new col I, viz:
In A4:
=INDEX(wkly!D$2$5,MATCH(LARGE(wkly!$I$2:$I$5,ROWS($1:1)),wkly!$I$2:$I$5,0))
Copy A4 to B4

In C4:
=INDEX(wkly!H$2:$H5,MATCH(LARGE(wkly!$I$2:$I$5,ROWS($1:1)),wkly!$I$2:$I$5,0))
Select A4:C4, copy down to C7

Repeat construct likewise for the other ranges

Note: Pl ask only one question in one posting.

---

 

[jeel]

I tried to upload a sample file at senduit.com.
I can't figure out how to make it a link for you.