Extract unique values based on criteria

[NeverSayDie]

Hello everybody, my 1st post.
I have checked for similiar posts here but not quite what I need.
I need assistance with a formula to return all unique values pertaining to just one criteria from an ever expanding data base. It is to be non VBA and not using filters. The data is Horse Racing results entered on a sheet called 'Race Resuts'.
On that sheet I have named ranges:- 'TRK' = Col 'C' and 'DIST' = Col 'G'
On sheet 'TRACK DETAILS' in $B$5 I enter the Track Name and then from $A$8:$A$37 I want the ALL the Unique distances related to that track to be inputed. I have a helper Col $I$8:$I$37 with numbers 1 to 30 for the formula I tried [that doesn't bring unique values but all repeated values as well].

I tried this failed formula:-
{=IF(ISERROR(LARGE(IF(TRK,=$B$5,DIST),"",LARGE(IF(TRK=$B$5,DIST),$I8))} .... copied down. My guess is that I may have to use ROW ... but I have tried and failed because it is beyond my capability.

Thanks for any help.

 

[GlennUK]

Have another helper column next to I8:I37, say in J8:J37 of formulas to fetch only distances for the chosen Track Name, like:

=IF(INDEX(TRK,I8)=$B$5,INDEX(DIST,I8),"")
copied down

And then use this array formula in cell A8 ( entered using Ctrl-Shiftr-Enter instead of Enter ):
=IF(ISNA(MATCH(0,COUNTIF($A$7:A7,$J$8:$J$39),0)),"",INDEX($J$8:$J$39,MATCH(0,COUNTIF($A$7:A7,$J$8:$J$39),0)))
( assuming that A7 is blank )
... and copy down.

 

[NeverSayDie]

THANKS FOR YOUR REPLY GLENN. IT HASN'T WORKED EITHER REGRETFULLY. THE NEW HELPER COLUMN SEEMS TO BE THE PROBLEM, IT JUST DRAWS BLANKS " ". THE RACE RESULTS ARE ENTERED ON THE DAYS THAT THEY ARE RUN. e.g AFTER A RACE MEETING AT SAY 'FLEMINGTON' IS RUN IT IS ENTERED AND THEY MAY NOT RUN THERE FOR ANOTHER WEEK OR SO, SO THEN THERE ARE 2030 OTHER MEETINGS ENTERED AFTER THAT. THEY ARE NOT JUST ENTERED UNDER THEIR INDIVIDUAL TRACKS AND THERE ARE THOUSANDS OF THEM EVER INCREASING.
HTH AND THANK YOU.
LES.

 

[GlennUK]

Well, what are the entries in TRK, and do any them match the contents of cell $B$5? If that is so, it should bring across the corresponding cell in DIST, into the new helper column.

Have you left the numbers 1,2,3 etc in the old helper column ( that's the I8 cell downwards as referenced in the formula )?

 

[NeverSayDie]

GLENN THE RACE RESULTS ARE ENTERED INTO 17 COLUMNS ACROSS UNDER DIFFERENT HEADINGS ON THE 'RACE RESULTS' SHEET, TWO OF WHICH ARE TRK & DIST FROM WHICH I'M TRYING TO COLLECT ALL THE UNIQUE DISTANCES RELATING TO EACH TRACK WHEN THE TRACK NAME IS ENTERED INTO $B$3. IT DISPLAYS MANY OTHER THINGS ALSO ABOUT THE ENTERED TRACK NAME.
EACH TRACK HAS DIFFERENT FIXED DISTANCES OVER WHICH THEY RACE [ABOUT 10] AND WITH A MOVEABLE RUNNING RAIL CAN EXTEND THOSE NUMBERS TO ABOUT 30 + .

TRK DIST[m]
FLEM 1000
" 1100
" 1200
" 1400
" 1600
" 2000
" 2400 .....etc
ROSE 1500
" 1300
" 1200
" 1100 .....etc
THE NEXT MEETING THEY MOVE THE RAIL OUT 6m FOR TURF PROTECTION AND REGROWTH AND YOU GET ....
FLEM 1006
" 1106
" 1206 .... etc
AND IF THE NEXT MEETING THE RAIL IS MOVED 12m YOU GET
FLEM 1012
" 1112 .... etc
WHAT I'M TRYING TO ACHIEVE WITH THE FORMULA WHEN I ENTER INTO $B$3 ON THE 'TRACK DETAILS' PAGE IS THIS ...

FLEM ◄ $B$3

DISTANCES
1000 ◄ $A6:$A$37 ▼
1006
1012
1100
1106
1200
1206 .... etc

I HOPE THIS MAKES MORE SENSE AND THANKS AGAIN.
LES

 

[GlennUK]

Well, let's start with the new helper column.

Your layout seems slightly different from your previous description ... so your old helper column should be in column I, with I6 =1, and I7=2, and I8=3 etc etc ... is that right?

Your new helper column would be in column J, with J6 having this formula:

=IF(INDEX(TRK,I6)=$B$3,INDEX(DIST,I6),"")

... copied down.

Do you get any results at all in the new helper column when you do this? We should get this part working before going on to the next step, which will bring the non-blank results into cells A6:A37. I'll wait until you can confirm that the first step is working.

PS - WRITING ALL OF YOUR TEXT IN CAPITALS IS LIKE SHOUTING ... DON'T DO IT, UNLESS YOU MEAN TO SHOUT.

 

[NeverSayDie]

Thanks for the PS. I has no idea it mean't that. I was just being lazy by leaving the Cap lock on. My wife told me that some time ago and I said 'It's nonsense, who would think that I was shouting ?' Oooops... sorry Glenn .... and to her.

Yes column 'J' does produce results. The meeting I'm doing to-night has only 13 unique distatnces. It draws from the 'Dist' column only 8 but 2 are duplicated and the 1st 5 cells [J6:J10] are blank and then the next 8 are filled and then blank.

Thanks.
'No capitals again Les'.

 

[GlennUK]

That's good. It seems like the new helper column is working ... now we should be able to extract the unique values with this array formula ( entered using Ctrl-Shift-Enter ) ... in cell A6:

=IF(ISNA(MATCH(0,COUNTIF($A$5:A5,$J$6:$J$37),0)),"",INDEX($J$6:$J$37,MATCH(0,COUNTIF($A$5:A5,$J$6:$J$37),0)))

... and copy down.

Note: this formula assumes that cell A5 is blank.

PS. thanks for not shouting this time ... ha ha, and you should take notice of what your wife says next time.

 

[NeverSayDie]

Ok I've done that but it has only cleaned up the the duplicates from the 2nd helper column and produced 6 of the 13 unique distances. i.e. It got rid of the 2 duplicates but it didn't retrieve the other seven unique distances.

Les.

 

[GlennUK]

Show the contents of the helper column, and show what results you get with the formula I gave you to fetch uniques from it.