Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to prevent duplicates using index,match & large
Hey Guys. I'm using index, match & large to sort a list of NFL teams by their point totals. There are a 2 teams with the same point total. Instead of listing both the teams my formula list the the 1st with the same point total twice. What do I need to add to my formula. INDEX($B$3:$B$10,MATCH(LARGE($A$3:$A$10,ROW(A1)),$ A$3:$A$10,0))
Its seems like it should be an easy solution but I've watched video's & read forums but can't find the solution I need. Thanks for any help! Ive tried attached a file but was having problems. Tried to put the data below. ----------------------- Page 1----------------------- A B C D E 1 Data Data Pts Tm 2 8 Dal 31 BAL 3 19 Pit 19 Pit 4 9 @ATL 15 PHI 5 15 PHI 12 @DEN 6 31 BAL 9 @ATL 7 12 @DEN 9 @ATL Cell e7 should match b9 instead of listing Atl 8 5 @BUF 8 Dal 9 9 IND 5 @BUF |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to prevent duplicates using index,match & large
Hi,
Am Sat, 11 Jul 2015 15:29:03 +0100 schrieb stumjumper: A B C D E 1 Data Data Pts Tm 2 8 Dal 31 BAL 3 19 Pit 19 Pit 4 9 @ATL 15 PHI 5 15 PHI 12 @DEN 6 31 BAL 9 @ATL 7 12 @DEN 9 @ATL Cell e7 should match b9 instead of listing Atl 8 5 @BUF 8 Dal 9 9 IND 5 @BUF your sorted points in column D. Then in E2: =INDEX(B:B,SMALL(IF(A$2:A$10=D2,ROW($2:$10)),COUNT IF(D$2:D2,D2))) Press CTRL+Shift+Enter to enter the array formula and copy down Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
|
|||
|
|||
Thanks for the response Claus. That did it. Appreciate it!
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index, Large and Match?? | Excel Worksheet Functions | |||
INDEX/MATCH/LARGE returning #VALUE! error | Excel Worksheet Functions | |||
Large Index Match Lookup | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |