Informational Search

[Guest]

Here's the Senario,

Sheet One Contain the Results of a Race:
Column A: Racers Name (in ABC order)
Column B: Their Hometown
Column C: Their Time
Column D: Their Heat Number

On Sheet 2 I want to have the spreadsheet seach for the proper infomation
from sheet 1.
The Colums are the same, and here, the row numbers represent the place number.

In Column C, I want it to find the lowest time first, then in the 2nd row,
find the 2nd lowest time, ect.

In the other three column, I want it to match the data listed next to the
time from sheet one, in their appropriate columns.

I hope you can figure out what I mean.

Thank you.

 

[T. Valko]

Why don't you just sort sheet1 based on the time?

If you don't want to or can't do that then how about just making a copy of
sheet1 and then sorting on the copied sheet.

 

[Guest]

I'm going to change the senario,

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the place
they are from, that is next to that number on sheet one will apear.

Eventually I will come back to putting them in numerical order. Also, I
don't want to have to copy the sheet and use the "Data Sort" button.

 

[T. Valko]

Assume Sheet2 A1 is where you enter the number.

Enter this formula in B1 and copy across to C1:

=IF($A1="","",VLOOKUP($A1,Sheet1!$A:$C,COLUMNS($B1:C1),0))

 

[Guest]

Now for the Second Part.

Sheet one,
Column A: a bunch of numbers in a random order.
Column B: nothing
Column C:

C1: =(min(A1:A50)
Rest of the C Column,
I want it to be the next number to be the next number in numerical order.
i.e. C2 >= C1, but only then next largest number.

Hope this makes sence.

 

[Pete_UK]

Try this:

C1: =SMALL(A$1:A$50,ROW(A1))

then copy that into C2:C10 to get the smallest, next smallest etc, as
the ROW function will return 1, 2, 3 etc as you copy it down.

Hope this helps.

Pete

 

[Guest]

There is a slight change here.

Sheet One lists:
Column 1: A number
Column 2: A person's Name
Column 3: The place they are from.

On Sheet two,
Column 1: I want to be able to enter a number.
Column 2: Based on the number in column 1, sheet 2 that I entered, the
person's name that is next to that number on sheet one will apear.
Column 3: Based on the number in column 1, sheet 2 that I entered, the place
they are from, that is next to that number on sheet one will apear.

Now, there's a curve ball, Sheet one has the numbers repeating. So I want
to makesure the correct number matches the correct name, then I'll base the
place they are from off of the name which can only appear once in the list.

Thanks to several responces, I managed to create the page worksheet to
interact like I listed below.

 

[T. Valko]

Now, there's a curve ball, Sheet one has the numbers repeating.

Do you mean that there are different names with the same number?

1...Joe...California
7...Sue...Georgia
6...Bob...Ohio
1...Sam...New York

Column 1: I want to be able to enter a number.

So, if you enter number 1 which line of info do you want?

 

[Guest]

Yeah, that's basically what I mean, but I forgot to include something in my
previous message. The list is in alphabetical order by the name. so your
list would be...

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

Also, please disreguard my message, "Number Order"

Thanks and sorry.

 

[T. Valko]

It's still not clear (to me!) what you want to do.

Based on the first post of this thread it sounds to me like you're just
duplicating you data but in a different order. That's why I suggested just
sorting it or making a copy and sorting that copy.

If you have duplicate numbers and you want to do a lookup based on a number
it can be complicated. I assume if you enter the number 1 then you want all
the info for *every instance* of 1:

6...Bob...Ohio
1...Joe...California
1...Sam...New York
7...Sue...Georgia.

See if this sample file is what you had in mind:

DeleteMe.xls 16kb

http://cjoint.com/?knxBzcL3yL

Select a number from the drop down and the list populates based on that
selection.

The formulas used to do this are array formulas. Array formulas need to be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

 

[Pete_UK]

Hi Biff,

I thinks he wants one sheet to show the data in name order and another
sheet to contain exactly the same data but sorted in order of time to
finish the race, i.e. sorting by formula rather than manually.

Pete

 

[Guest]

Biff,

That's pretty much what I mean, but is there anyway to continue that down
the list?When I entered "5" in F2, I couldn't enter 1 in F% to get those
names, is there a way to do that?

Also, I apologize if I am taking up too much of your time.

 

[T. Valko]

I apologize if I am taking up too much of your time.

Nah, don't worry about it. THIS is what I do with my time!

Ok, see this sample file. This is what you wanted to do from your
description in your very first post. The formula in column G is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

http://cjoint.com/?kpfOViFyIX

 

[Guest]

Biff,

This is brilliant, thank you very, very much. See I kept track of times for
my high school's track team, I was looking for a way to enter times and what
not using excell and these formulas.

Again thank you very much. Also, what did you mean by, "Array formulas need
to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)"?

 

[T. Valko]

You're welcome. Thanks for the feedback!

Array formulas are a special kind of formula. When entering a regular
formula you simply type the formula then you hit the ENTER key to enter it
in a cell. Array formulas are different. You type the formula, then, instead
of hitting the ENTER key you *must* use a combination of keys. Those keys
are CTRL, SHIFT and ENTER. That is, hold down both the CTRL key and the
SHIFT key then hit ENTER. When done properly Excel will enclose the formula
in squiggly braces { }. You can not just type these braces in. You *must*
use the key combo to generate them. Also, anytime you edit an array formula
you must re-enter it as an array with the key combo.

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Guest]

Biff,

The equation works weel, but it there anyway to get it to observe more
information, because this is allowing me to look at the top four numbers/time
on the list, I would like to be able to look at 50-60 different numbers, I've
tried various things, but I cant figure out the propper numbers to change.

Thanks.

 

[Guest]

I need to make another change, I did end up figuring out how to get more
date, but when a cell (in the number column) is blank it acts as though it
were zero, it there a way to avoid this? If so, how?

Thanks and sorry.

 

[T. Valko]

Based on the most recent sample file, change the formula in F2 and copy down
until you get blanks:

=IF(ROWS(F$2:F2)<=COUNT(A$2:A$5),SMALL(A$2:A$5,ROWS(F$2:F2)),"")