Sorting Numbers, with Friends, by Formula?
 

Hello,
I am trying to sort a column of numbers, that are not in order, into
another column. I have used this formula
naming the range Rolls, to sort the numbers ascending (straight out of
Excel Formulas 2003):
=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls ))))),"",SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls) ))))
This works great, But......There is another column of data associated
with these numbers, which is the adjascent cell to each number.
Is there a way to sort the numbers and take the adjascent cell with
them? I can use as many columns as i need,
but i can't figure it out.
I am having a lot of trouble with this one, in which a macro cannot be
used.

Nice to be back here!
Thanks for any help!
Steve

Sorting Numbers, with Friends, by Formula?
 

Assume your formula starts in A2, then in B2 put this formula:

=INDEX(OFFSET(Rolls,0,1),MATCH(A2,Rolls,0),1)

and drag fill down the column.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Hello,
I am trying to sort a column of numbers, that are not in order, into
another column. I have used this formula
naming the range Rolls, to sort the numbers ascending (straight out of
Excel Formulas 2003):
=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls ))))),"",SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls) ))))
This works great, But......There is another column of data associated
with these numbers, which is the adjascent cell to each number.
Is there a way to sort the numbers and take the adjascent cell with
them? I can use as many columns as i need,
but i can't figure it out.
I am having a lot of trouble with this one, in which a macro cannot be
used.

Nice to be back here!
Thanks for any help!
Steve

Sorting Numbers, with Friends, by Formula?
 

On Mar 22, 8:16 pm, "Tom Ogilvy" wrote:
Assume your formula starts in A2, then in B2 put this formula:

=INDEX(OFFSET(Rolls,0,1),MATCH(A2,Rolls,0),1)

and drag fill down the column.

--
Regards,
Tom Ogilvy

wrote in message

oups.com...



Hello,
I am trying to sort a column of numbers, that are not in order, into
another column. I have used this formula
naming the range Rolls, to sort the numbers ascending (straight out of
Excel Formulas 2003):
=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls ))))),"",SMALL(Rolls,ROW(*INDIRECT("1:"&ROWS(Rolls )))))
This works great, But......There is another column of data associated
with these numbers, which is the adjascent cell to each number.
Is there a way to sort the numbers and take the adjascent cell with
them? I can use as many columns as i need,
but i can't figure it out.
I am having a lot of trouble with this one, in which a macro cannot be
used.

Nice to be back here!
Thanks for any help!
Steve- Hide quoted text -

- Show quoted text -

Wow, Big Tom, Thanks for the replay!!
I'm sorry to say, i'm close, but no cigar.
Did i mention this is an array formula (i'm sure you knew anyway). I
am getting an #N/A return.
Here is an exact situation:
Col A Col B
3 1
6 1
7 1
8 1
10 1
12 1
1 2
2 2
4 2
5 2
9 2
11 2

And i need it to look like this:
Col C Col D
1 2
2 2
3 1
4 2
5 2
6 1
7 1
8 1
9 2
10 1
11 2
12 1

where col C now has the array formula:
{=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Roll s))))),"",SMALL(Rolls,ROW(*
INDIRECT("1:"&ROWS(Rolls)))))}
and range named Rolls is the Col A numbers

I put your formula in Col D:
=INDEX(OFFSET(Rolls,0,1),MATCH(C2,Rolls,0),1)
, but am getting #N/A returned.
What am i doing wrong; seems like it should work?

Thanks for your help,
Steve

Sorting Numbers, with Friends, by Formula?
 

On Mar 22, 10:04 pm, wrote:
On Mar 22, 8:16 pm, "Tom Ogilvy" wrote:





Assume your formula starts in A2, then in B2 put this formula:

=INDEX(OFFSET(Rolls,0,1),MATCH(A2,Rolls,0),1)

and drag fill down the column.

--
Regards,
Tom Ogilvy

wrote in message

roups.com...

Hello,
I am trying to sort a column of numbers, that are not in order, into
another column. I have used this formula
naming the range Rolls, to sort the numbers ascending (straight out of
Excel Formulas 2003):
=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Rolls ))))),"",SMALL(Rolls,ROW(**INDIRECT("1:"&ROWS(Roll s)))))
This works great, But......There is another column of data associated
with these numbers, which is the adjascent cell to each number.
Is there a way to sort the numbers and take the adjascent cell with
them? I can use as many columns as i need,
but i can't figure it out.
I am having a lot of trouble with this one, in which a macro cannot be
used.

Nice to be back here!
Thanks for any help!
Steve- Hide quoted text -

- Show quoted text -

Wow, Big Tom, Thanks for the replay!!
I'm sorry to say, i'm close, but no cigar.
Did i mention this is an array formula (i'm sure you knew anyway). I
am getting an #N/A return.
Here is an exact situation:
Col A Col B
3 1
6 1
7 1
8 1
10 1
12 1
1 2
2 2
4 2
5 2
9 2
11 2

And i need it to look like this:
Col C Col D
1 2
2 2
3 1
4 2
5 2
6 1
7 1
8 1
9 2
10 1
11 2
12 1

where col C now has the array formula:
{=IF(ISERR(SMALL(Rolls,ROW(INDIRECT("1:"&ROWS(Roll s))))),"",SMALL(Rolls,ROW*(*
INDIRECT("1:"&ROWS(Rolls)))))}
and range named Rolls is the Col A numbers

I put your formula in Col D:
=INDEX(OFFSET(Rolls,0,1),MATCH(C2,Rolls,0),1)
, but am getting #N/A returned.
What am i doing wrong; seems like it should work?

Thanks for your help,
Steve- Hide quoted text -

- Show quoted text -

Never Mind!!!!!!!!!!!!!!!!!!!!!!!!!

You're the Master.........
I'm the Idiot..................

That Works Like A Charm!

Thanks so much for your help,

Have a Great Weekend!!!