sort column corresponding to second column

F

flarunner

I have 3 columns of data:
Column A: Daily Miles Run
Column B: Daily Pace of Run
Column C: Sorted Miles

The following formula (copied down) was used to get the Sorted Miles in
Column C:

=IF(COUNT(A$2:A$8)>=ROWS($1:1),SMALL(A$2:A$8,ROWS($1:1)),"")

This is what it looks like:

A B C
3 8:21 3
4 8:38 4
6 8:55 4
5 8:46 5
4 8:45 5
5 8:32 6

I would like to get the Daily Paces (Column B) to also be sorted from
smallest to largest AND to correspond to the Sorted Miles (Column C).

I have used various LOOKUP, INDEX and MATCH formulas, but they return the
FIRST INSTANCE of repeated numbers, and keep repeating that number, as shown
below in Column D:

A B C D
3 8:21 3 8:21
4 8:38 4 8:38
6 8:55 4 8:38
5 8:46 5 8:46
4 8:45 5 8:46
5 8:32 6 8:55


How can I make Column D read as follows?

D
8:21
8:38
8:45
8:32
8:46
8:55

Thanks for any and all help.
 
S

Sheeloo

Use
=IF(COUNT(A$2:A$8)>=ROWS($1:1),SMALL(B$2:B$8,ROWS($1:1)),"")

You already had the solution... simple change A to B within SMALL
 
F

flarunner

Thanks for responding, but alas, the formula you posted just sorts the Pace
values.

What I want is to sort the Mile values (Column C) and then sort the Pace
values (Column D) so that they correspond to the correct Mile values (Column
C).

The example below shows how it should look:

A B C D
3 8:21 3 8:21
4 8:38 4 8:38
6 8:55 4 8:45
5 8:46 5 8:32
4 8:45 5 8:46
5 8:32 6 8:55

Thanks so much.
 
S

Sheeloo

Try the array formula (CTRL-SHIFT-ENTER after typing

=LARGE(IF($A$2:$A$7=D2,$B$2:$B$7,""),COUNTIF($D2:$D$7,D2))
 
F

flarunner

THAT'S what I wanted!
Thank you very much!

Sheeloo said:
Try the array formula (CTRL-SHIFT-ENTER after typing

=LARGE(IF($A$2:$A$7=D2,$B$2:$B$7,""),COUNTIF($D2:$D$7,D2))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top