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

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

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

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!!!