LinkBack URL
About LinkBacks
I have 4 Coulms named NUMBER, OWNER NAME, DOG NAME, TIME, I want to show on a seperate sheet (MASTER), which NUMBER, OWNER NAME, DOG NAME, TIME, had the fastest time. Dont really know where to start!
I have 4 Coulms named NUMBER, OWNER NAME, DOG NAME, TIME, I want to show on a seperate sheet (MASTER), which NUMBER, OWNER NAME, DOG NAME, TIME, had the fastest time. Dont really know where to start!
Last edited by dadrew; 04-28-2013 at 12:56 PM.
you could rank the dogs and extract based on ranking - But if all you need is min
=IFERROR(INDEX(Sheet1!$D$2:$D$7, MATCH(0, COUNTIF($D1:D$1, Sheet1!$D$2:$D$7), 0)),"") - enter with control+shift+enter for an array formula
should extract the time onto a new sheet
but i would use rank only issue would be if two times are identical - what happens then
i will produce an example
Your idea is better, unlikely that times will be the same as its done to 10ths of a second! Look forward to the example. Many thanksOriginally Posted by etaf
Perhaps first adapt your thread title per forum rules?
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
i have made up the sheet and showed a ranking and now pulled all the data across to a new sheet
Excellent, many thanks.
@ dadrew, Im glad you got a solution to your question
For future reference, (and you have been a member long enough to know this) please use thread titles that actually describe your problem
How would I change this to transfer just the top three dogs across please?
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.
If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc
i have an alternative and better way to provide the extract and the top 3
But before I reply, would you comply to the admin requests please
Thank you
Done thanks
i would suggest you use a pivot table as that has a lot more flexibility and you can show the top X number
I have taken the raw data file and insert table
That now allows you to add more dogs and with a pivot table it will refresh the data (by clicking on the reset)
you can then set the value to show - Bottom 3 values of time and then the pivot is immediately filtered, even on a refresh
may be a much neater way of doing it , allowing for greater control and filtering
see attached sheet pvt2 - which uses just the table of 4 columns
PVT - uses the rank - but needs a lot more formula changes
see what you think - i can answer any questions
OK I like the PVT2 idea. I did find an issue that when I added a new record with a time of 00:00:03 and refreshed All the top record on PVT2 was blank and the new one came in on the next line!
i just added a few at it produces 4 if theres a tie and 3 if not - seems to work with a refresh
see attached
Humm I just added a line to Timing Number 15, Owner Dave Dog Storm Time 00:00:01. I did refresh data and on the PVT2 sheet I got:
Number Owner Dog Sum of Time
(blank) (blank) (blank)
15 Dave Storm 00:00:01
13 Ted lassie 00:00:02
12 Wayne Snowy 00:00:02
isn't that correct ?
Opss you mean the blank lines
OK
Last edited by etaf; 04-28-2013 at 02:47 PM.
Not sure why you have a blank- have you missed a row on the table OUT
anyway on the pivot table
click on the arrow next the number on the pivot
you should see a list and it will have blank in - so you untick blank
But unless you have a blankk row in the table - it should not show
actually clicking on the number and removing blank does not work - as it stops the filter for bottom 3
so i used the owner and clicked on arrow and unticked blank
then it worked ok
you must have a blank row in the table somewhere
Last edited by etaf; 04-28-2013 at 02:53 PM.
I have no blanks in the table, but it happens whenever I add a record to the table
can you post the spreadsheet please > advanced> manage attachments > upload the file
what version of excel
File attached, I have added one person a dog and a time
you have clicked in the next row - see the blue line around the table and so the pivot sees that row as a blank and included - delete the row 17
and when you add a name dont click down to the next row and leave a blank row - just stay in the row you enter data into
that fixes it
Many thanks for your help
your welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks