Automatically Sort By Reference Amount?
 

I have two worksheets in one document and would like to reference totals from
one worksheet and display the results (by highest total, first) on the second
worksheet. For example:

Worksheet 1

SOLD Week 1 Week 2 Total Sold

Oranges 2 3 5
Pears 0 1 1
Apples 5 5 10


On Worsheet 2, I would like to show the following:

Highest Sold:

Apples 10
Oranges 5
Pears 1

Not sure how I would display this so that Apples (in this case, the highest
total) would appear on top and Pears (in this case, the lowest total) would
appear on the bottom.

Any help that you can provide is welcome. Thanks.

G

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

Thanks, Stefi. Follow-up comments/questions:

- I understand the Column B RESULTS code (=LARGE(Sheet1!D$3:D$5;1). I
tested it and it works well.

- I'm not clear how Column A RESULTS code (the
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) will get the corresponding name for each
Column B RESULTS.

In this example, the HIGHEST is Apples and 10 appears at the top of Column B
RESULTS. However, I can't get "Apples" to appear in Column A RESULTS.

Shouldn't we be referencing Column B RESULTS to get the corresponding name?

Can you provide further assistance? Thanks.

Gary


"Stefi" wrote:

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

Hi Gary,

- I'm not clear how Column A RESULTS code (the
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) will get the corresponding name for each
Column B RESULTS.

In this example, the HIGHEST is Apples and 10 appears at the top of Column B
RESULTS. However, I can't get "Apples" to appear in Column A RESULTS.

Shouldn't we be referencing Column B RESULTS to get the corresponding name?


We do reference to column B:

In line 3: VLOOKUP(B3...
In line 4: VLOOKUP(B4...
etc.

=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) looks for B3 value (10 in our example)
in the leftmost column of range Sheet1!D$3:E$5 (that is column D of sheet1)
and returns the corresponding value in the row of hit from the 2nd (3rd
argument) column (that is column E) of the range.

10 is found in row 5 in sheet1 column D, the corresponding value in the same
row in column E is Apples.

Perhaps you didn't recognize that the second argument in row 3
=LARGE(Sheet1!D$3:D$5;1)
is incremented by 1 in each following rows:
in row 4:
=LARGE(Sheet1!D$3:D$5;2)
representing the rank: 1st, 2nd ... element of the sequence is to be placed
there.

Here is an improved function, that takes this rank from its position:
=LARGE(Sheet1!D$3:D$5;CELL("row";A3)-2) in row 3 (-2 is used becouse of
the first two header rows).

This can be copied downwards!

Make sure, that column E in sheet1 contains correctly the values in column A.

If you provide an e-mail address I send you a sample workbook.

Regards,
Stefi


"Stefi" wrote:

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

Thanks, Stefi ... that's clear. Everything is working well, except for one
thing ... for duplicate totals, the first referenced name appears for all of
them. For example, if the total for Apples = 10 and the total for Oranges =
10, then the output is as follows:

Apples 10
Apples 10

Any way around this?

Thanks, again.

Gary

"Stefi" wrote:

Hi Gary,

- I'm not clear how Column A RESULTS code (the
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) will get the corresponding name for each
Column B RESULTS.

In this example, the HIGHEST is Apples and 10 appears at the top of Column B
RESULTS. However, I can't get "Apples" to appear in Column A RESULTS.

Shouldn't we be referencing Column B RESULTS to get the corresponding name?


We do reference to column B:

In line 3: VLOOKUP(B3...
In line 4: VLOOKUP(B4...
etc.

=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) looks for B3 value (10 in our example)
in the leftmost column of range Sheet1!D$3:E$5 (that is column D of sheet1)
and returns the corresponding value in the row of hit from the 2nd (3rd
argument) column (that is column E) of the range.

10 is found in row 5 in sheet1 column D, the corresponding value in the same
row in column E is Apples.

Perhaps you didn't recognize that the second argument in row 3
=LARGE(Sheet1!D$3:D$5;1)
is incremented by 1 in each following rows:
in row 4:
=LARGE(Sheet1!D$3:D$5;2)
representing the rank: 1st, 2nd ... element of the sequence is to be placed
there.

Here is an improved function, that takes this rank from its position:
=LARGE(Sheet1!D$3:D$5;CELL("row";A3)-2) in row 3 (-2 is used becouse of
the first two header rows).

This can be copied downwards!

Make sure, that column E in sheet1 contains correctly the values in column A.

If you provide an e-mail address I send you a sample workbook.

Regards,
Stefi


"Stefi" wrote:

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

Hi Gary,
True, unfortunately even the RANK function returns identical numbers for
cells with the same value!
What about simply copying columns SOLD and TOTAL to sheet2, then sort sheet2
by column total. It could be done by a simple macro!
Regards,
Stefi


€žG€� ezt Ã*rta:

Thanks, Stefi ... that's clear. Everything is working well, except for one
thing ... for duplicate totals, the first referenced name appears for all of
them. For example, if the total for Apples = 10 and the total for Oranges =
10, then the output is as follows:

Apples 10
Apples 10

Any way around this?

Thanks, again.

Gary

"Stefi" wrote:

Hi Gary,

- I'm not clear how Column A RESULTS code (the
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) will get the corresponding name for each
Column B RESULTS.

In this example, the HIGHEST is Apples and 10 appears at the top of Column B
RESULTS. However, I can't get "Apples" to appear in Column A RESULTS.

Shouldn't we be referencing Column B RESULTS to get the corresponding name?


We do reference to column B:

In line 3: VLOOKUP(B3...
In line 4: VLOOKUP(B4...
etc.

=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) looks for B3 value (10 in our example)
in the leftmost column of range Sheet1!D$3:E$5 (that is column D of sheet1)
and returns the corresponding value in the row of hit from the 2nd (3rd
argument) column (that is column E) of the range.

10 is found in row 5 in sheet1 column D, the corresponding value in the same
row in column E is Apples.

Perhaps you didn't recognize that the second argument in row 3
=LARGE(Sheet1!D$3:D$5;1)
is incremented by 1 in each following rows:
in row 4:
=LARGE(Sheet1!D$3:D$5;2)
representing the rank: 1st, 2nd ... element of the sequence is to be placed
there.

Here is an improved function, that takes this rank from its position:
=LARGE(Sheet1!D$3:D$5;CELL("row";A3)-2) in row 3 (-2 is used becouse of
the first two header rows).

This can be copied downwards!

Make sure, that column E in sheet1 contains correctly the values in column A.

If you provide an e-mail address I send you a sample workbook.

Regards,
Stefi


"Stefi" wrote:

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

This is the macro:

Sub copysort()
Worksheets("Sheet1").Range("A:A,D:D").Copy _
Destination:=Worksheets("Sheet2").Range("A1")
Sheets("Sheet2").Select
Range("A1").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub

Place it in a normal module, assign a hot key and try!

Regards,
Stefi


€žG€� ezt Ã*rta:

Thanks, Stefi ... that's clear. Everything is working well, except for one
thing ... for duplicate totals, the first referenced name appears for all of
them. For example, if the total for Apples = 10 and the total for Oranges =
10, then the output is as follows:

Apples 10
Apples 10

Any way around this?

Thanks, again.

Gary

"Stefi" wrote:

Hi Gary,

- I'm not clear how Column A RESULTS code (the
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) will get the corresponding name for each
Column B RESULTS.

In this example, the HIGHEST is Apples and 10 appears at the top of Column B
RESULTS. However, I can't get "Apples" to appear in Column A RESULTS.

Shouldn't we be referencing Column B RESULTS to get the corresponding name?


We do reference to column B:

In line 3: VLOOKUP(B3...
In line 4: VLOOKUP(B4...
etc.

=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) looks for B3 value (10 in our example)
in the leftmost column of range Sheet1!D$3:E$5 (that is column D of sheet1)
and returns the corresponding value in the row of hit from the 2nd (3rd
argument) column (that is column E) of the range.

10 is found in row 5 in sheet1 column D, the corresponding value in the same
row in column E is Apples.

Perhaps you didn't recognize that the second argument in row 3
=LARGE(Sheet1!D$3:D$5;1)
is incremented by 1 in each following rows:
in row 4:
=LARGE(Sheet1!D$3:D$5;2)
representing the rank: 1st, 2nd ... element of the sequence is to be placed
there.

Here is an improved function, that takes this rank from its position:
=LARGE(Sheet1!D$3:D$5;CELL("row";A3)-2) in row 3 (-2 is used becouse of
the first two header rows).

This can be copied downwards!

Make sure, that column E in sheet1 contains correctly the values in column A.

If you provide an e-mail address I send you a sample workbook.

Regards,
Stefi


"Stefi" wrote:

Put an E helper column in sheet1 which repeats column A

In your example:

Worksheet 1
Column A B C D E
SOLD Week 1 Week 2 Total Sold E

Oranges 2 3 5 =A3
Pears 0 1 1 =A4
Apples 5 5 10 =A5


On Worsheet 2, :

Highest Sold:
column A B
=VLOOKUP(B3;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;1)
=VLOOKUP(B4;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;2)
=VLOOKUP(B5;Sheet1!D$3:E$5;2;FALSE) =LARGE(Sheet1!D$3:D$5;3)
Regards,
Stefi

Hi Gary,

Here is another trick to eliminate duplicate totals:

Put E,F,G helper columns in sheet1 as follows:

On Worksheet 1

Column A B C D E F G
SOLD Week1 Week2 Total Sold
Oranges 2 3 5 =D2+RAND() =A2 =D2
Pears 0 1 1 =D3+RAND() =A3 =D3
Apples 5 5 10 =D4+RAND() =A4 =D4


Put C helper column in sheet2 as follows:

On Worksheet 2

column A
Highest Sold
=VLOOKUP(C2;Sheet1!D$2:E$4;2;FALSE)
=VLOOKUP(C3;Sheet1!D$2:E$4;2;FALSE)
=VLOOKUP(C4;Sheet1!D$2:E$4;2;FALSE)

column B
=VLOOKUP(C2;Sheet1!E$2:G$4;3;FALSE)
=VLOOKUP(C3;Sheet1!E$2:G$4;3;FALSE)
=VLOOKUP(C4;Sheet1!E$2:G$4;3;FALSE)

column C
=LARGE(Sheet1!E$2:E$4;CELL("row";A2)-2)
=LARGE(Sheet1!E$2:E$4;CELL("row";A3)-2)
=LARGE(Sheet1!E$2:E$4;CELL("row";A4)-2)

Format sheet1!column E and sheet2!column C as numbers, 9 decimals!

Regards,
Stefi