Sort by Large, Offset from Match, Duplicate values problem

[JWC Excel]

Heya all,

I have a large list of dynamic data, which I am organizing in different
ways. I realize this can all be done in PivotTables, but we have to do this
the olde fashioned way. The table I am drawing the info from looks roughly
like this:

Worksheet: DATA

Player Buy-In Cash-Out Difference # Times entered
Joe SHMOE $930 $900 $30 1
Sam DOG $0 $0 $0 0
Peter BLAH $100 $1000 $900 22
Doug FINN $500 $0 $500 22

I am using =LARGE, =SMALL on the columns to sort the data, and then MATCH to
find that value, and OFFSET to get the other values in that line. I am using
this method because VLOOKUP can only search to the left of the reference, and
the majority of things I am looking up are in Column B, C D & E.

An example formula is sorting by number of entries on another sheet:
=LARGE(DATA!$E$2:$E$5,1) =LARGE(DATA!$E$2:$E$5,2) =LARGE(DATA!$E$2:$E$5,3)
=LARGE(DATA!$E$2:$E$5,4), etc.
This organizes the data into Most Frequent, in this case.
Then I use: =OFFSET(DATA!$E$2,MATCH($F3,DATA!$E:$E,0)-2,-2)
=OFFSET(DATA!$E$2,MATCH($F3,DATA!$E:$E,0)-2,-3)
=OFFSET(DATA!$E$2,MATCH($F3,DATA!$E:$E,0)-2,-4) to build the rest of the info
on that line. F3 would be the cell where the value of the LARGE is located.

Everything works fine and dandy until you come to matching values (22
visits). Now it returns both 22s, but Match will only return the "first" 22
value. So the table results would look like this:
Player Buy-In Cash-Out Difference # Times entered
Peter BLAH $100 $1000 $900 22
Peter BLAH $100 $1000 $900 22
Joe SHMOE $930 $900 $30 1
Sam DOG $0 $0 $0 0

Is there a way to modify my formula, perhaps with an IF statement to detect
duplicate values, to prevent the same value from being read over & over?

Thanks for any help you can offer!

~Jay

 

[Billy Liddel]

On the data sheet use this formula to create a nominal Rank of column E, so
in F2
enter
=IF(ISERROR(SMALL(Data!$F$2:F$5,ROW()-1)),"",SMALL(Data!$F$2:F$5,ROW()-1))

copy this down past your data as for as you like. This is the column that
we'll use to get the data in sheet 2. The formula is entered in Sheet2 F1

=IF(ISERROR(SMALL(Data!$F$2:F$5,ROW()-1)),"",SMALL(Data!$F$2:F$5,ROW()-1))

copy down as far as you wish.
In E2 enter this formula:

=IF($F$2="","",INDEX(Data!E:E,MATCH($F2,Data!$F:$F,0)))

Copy this formula across to a2 so this formula is now in A2 to E2.

Select A2:E2 and copy down as far as you wish.

Please click Yes if useful.

Peter

 

[Billy Liddel]

I must have missed the dynamic range in the formulas. The nominal rank can be
written in F2 of sheet Data! as:


=IF(A2="","",RANK($E2,$E:$E)+COUNTIF($E$2:$E2,$E2)-1)

F2 of the SortByFormulas sheet is:
=IF(ISERROR(SMALL(Data!$F:$F,ROW()-1)),"",SMALL(Data!F:F,ROW()-1))

E2 in same sheet is:
=IF($F2="","",INDEX(Data!E:E,MATCH($F2,Data!$F:$F,0)))

Copy this across to A2 then copy the rows of formulas down.

HTH
Peter