Sorting Numbers, with Friends, by Formula?

  • Thread starter Thread starter warp9ss
  • Start date Start date
W

warp9ss

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







- Show quoted text -
Click to expand...

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(Rolls))))),"",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
 
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(Rolls))))),"",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 -
Click to expand...

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