sort column corresponding to second column
 

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.

sort column corresponding to second column
 

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

"flarunner" wrote:

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.

sort column corresponding to second column
 

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.

"Sheeloo" wrote:

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

"flarunner" wrote:

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.

sort column corresponding to second column
 

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

"flarunner" wrote:

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.

"Sheeloo" wrote:

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

"flarunner" wrote:

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.

sort column corresponding to second column
 

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

"Sheeloo" wrote:

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

"flarunner" wrote:

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.

"Sheeloo" wrote:

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

"flarunner" wrote:

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.