software

Hi there,

Have spent hours trying to work this out! Hope someone can help.

My raw data is (A1:B6):
A 2
B 0
C 1
D 0
E 0
F 3

I want to sort column B values in descending order somewhere else in the
worksheet (say column K), and give the corresponding value from column A in
column J. Ie:

(J1:K6)
F 3
A 2
C 1
B 0
E 0
F 0

In order to calculate column K I have used: =large(B1:B6,n) where n = 1 to 6.
In order to calculate column J (say row 1) I have used:
=INDEX(A1:B6, MATCH(J1,B1:B6,0),1)

The problem with the MATCH (0) function is that it returns the first value
that is exaclt equal to the look up value. As such, some of the values
associated with the zeros are missed, and I get:
(J1:K6)
F 3
A 2
C 1
B 0
B 0
B 0

What can I do so that I get the following?
(J1:K6)
F 3
A 2
C 1
B 0
E 0
F 0

Thanks in advance!!

One play using non-array formulas ..

Put in K1:
=IF(ISERROR(LARGE($M:$M,ROW(A1))),quot;quot;,INDEX(A:A,MAT CH(LARGE($M:$M,ROW(A1)),$M
:$M,0)))
Copy K1 to L1

Put in M1: =IF(B1=quot;quot;,quot;quot;,B1-ROW()/10^10)

Select K1:M1, fill down to say, M50
to cover the max expected extent of data

Cols K amp; L will auto-return a full descending sort of cols A amp; B, sorted by
the values in col B, with all results neatly bunched at the top. Lines with
tied values in col B, if any, will be listed in the same relative order that
these appear within cols A amp; B.

For the sample data posted, we'd get:

F 3
A 2
C 1
B 0
D 0
E 0
(quot;blankquot; rows below)

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Smurfettequot; gt; wrote in message
...
gt; Hi there,
gt;
gt; Have spent hours trying to work this out! Hope someone can help.
gt;
gt; My raw data is (A1:B6):
gt; A 2
gt; B 0
gt; C 1
gt; D 0
gt; E 0
gt; F 3
gt;
gt; I want to sort column B values in descending order somewhere else in the
gt; worksheet (say column K), and give the corresponding value from column A
in
gt; column J. Ie:
gt;
gt; (J1:K6)
gt; F 3
gt; A 2
gt; C 1
gt; B 0
gt; E 0
gt; F 0
gt;
gt; In order to calculate column K I have used: =large(B1:B6,n) where n = 1 to
6.
gt; In order to calculate column J (say row 1) I have used:
gt; =INDEX(A1:B6, MATCH(J1,B1:B6,0),1)
gt;
gt; The problem with the MATCH (0) function is that it returns the first value
gt; that is exaclt equal to the look up value. As such, some of the values
gt; associated with the zeros are missed, and I get:
gt; (J1:K6)
gt; F 3
gt; A 2
gt; C 1
gt; B 0
gt; B 0
gt; B 0
gt;
gt; What can I do so that I get the following?
gt; (J1:K6)
gt; F 3
gt; A 2
gt; C 1
gt; B 0
gt; E 0
gt; F 0
gt;
gt; Thanks in advance!!
gt;
gt;
gt;
Thanks, Max. I've found another solution, which I wrote in a reply, but when
it came time to posting it, I had to relog in and it got lost! In any case,
I appreciated your help.

Cheers,
Debbie

quot;Maxquot; wrote:

gt; One play using non-array formulas ..
gt;
gt; Put in K1:
gt; =IF(ISERROR(LARGE($M:$M,ROW(A1))),quot;quot;,INDEX(A:A,MAT CH(LARGE($M:$M,ROW(A1)),$M
gt; :$M,0)))
gt; Copy K1 to L1
gt;
gt; Put in M1: =IF(B1=quot;quot;,quot;quot;,B1-ROW()/10^10)
gt;
gt; Select K1:M1, fill down to say, M50
gt; to cover the max expected extent of data
gt;
gt; Cols K amp; L will auto-return a full descending sort of cols A amp; B, sorted by
gt; the values in col B, with all results neatly bunched at the top. Lines with
gt; tied values in col B, if any, will be listed in the same relative order that
gt; these appear within cols A amp; B.
gt;
gt; For the sample data posted, we'd get:
gt;
gt; F 3
gt; A 2
gt; C 1
gt; B 0
gt; D 0
gt; E 0
gt; (quot;blankquot; rows below)
gt;
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Smurfettequot; gt; wrote in message
gt; ...
gt; gt; Hi there,
gt; gt;
gt; gt; Have spent hours trying to work this out! Hope someone can help.
gt; gt;
gt; gt; My raw data is (A1:B6):
gt; gt; A 2
gt; gt; B 0
gt; gt; C 1
gt; gt; D 0
gt; gt; E 0
gt; gt; F 3
gt; gt;
gt; gt; I want to sort column B values in descending order somewhere else in the
gt; gt; worksheet (say column K), and give the corresponding value from column A
gt; in
gt; gt; column J. Ie:
gt; gt;
gt; gt; (J1:K6)
gt; gt; F 3
gt; gt; A 2
gt; gt; C 1
gt; gt; B 0
gt; gt; E 0
gt; gt; F 0
gt; gt;
gt; gt; In order to calculate column K I have used: =large(B1:B6,n) where n = 1 to
gt; 6.
gt; gt; In order to calculate column J (say row 1) I have used:
gt; gt; =INDEX(A1:B6, MATCH(J1,B1:B6,0),1)
gt; gt;
gt; gt; The problem with the MATCH (0) function is that it returns the first value
gt; gt; that is exaclt equal to the look up value. As such, some of the values
gt; gt; associated with the zeros are missed, and I get:
gt; gt; (J1:K6)
gt; gt; F 3
gt; gt; A 2
gt; gt; C 1
gt; gt; B 0
gt; gt; B 0
gt; gt; B 0
gt; gt;
gt; gt; What can I do so that I get the following?
gt; gt; (J1:K6)
gt; gt; F 3
gt; gt; A 2
gt; gt; C 1
gt; gt; B 0
gt; gt; E 0
gt; gt; F 0
gt; gt;
gt; gt; Thanks in advance!!
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

Debbie, thanks for posting back.

No prob. Glad you found a solution to your taste.

Perhaps you could also post / share
the solution for the benefit of all in the newsgroup?
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Smurfettequot; gt; wrote in message
...
gt; Thanks, Max. I've found another solution, which I wrote in a reply, but
when
gt; it came time to posting it, I had to relog in and it got lost! In any
case,
gt; I appreciated your help.
gt;
gt; Cheers,
gt; Debbie
Hi Max,

See my solution below (hopefully this time it doesn't get lost!)

Raw data (A1:C6):
1 Apples 2
2 Bananas 0
3 Carrots 1
4 Durian 0
5 Eggs 0
6 Figs 3

Middle step (D16):
0
2
0
4
5
0

Column D equation is: =IF(C1=0,$A1,0)

Final Solution (E1:G6):
1 Figs 3
2 Apples 2
3 Carrots 1
4 Bananas 0
5 Durian 0
6 Eggs 0

whe
Column E numbers the rows of data
Column F equation is:
=IF(G1=0,INDEX($B$1:$D$6,LARGE(D$1$6,(ROWS($A$1: $A$5) 1-$E1)),1),INDEX($B$1:$D$6,MATCH(G1,C$1:C$6,0),1))
Column G equation is: =LARGE($C$1:$C$6,$E1)

Hope this makes sense. It works because the only number that gets repeated
in my case is '0'. Not sure how to make it more general. I've absolute
referenced cells so I can easily copy and paste.

Cheers,
Debbie

quot;Maxquot; wrote:

gt; Debbie, thanks for posting back.
gt;
gt; No prob. Glad you found a solution to your taste.
gt;
gt; Perhaps you could also post / share
gt; the solution for the benefit of all in the newsgroup?
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Smurfettequot; gt; wrote in message
gt; ...
gt; gt; Thanks, Max. I've found another solution, which I wrote in a reply, but
gt; when
gt; gt; it came time to posting it, I had to relog in and it got lost! In any
gt; case,
gt; gt; I appreciated your help.
gt; gt;
gt; gt; Cheers,
gt; gt; Debbie
gt;
gt;
gt;

gt; See my solution below (hopefully this time it doesn't get lost!)

Thanks for posting back. Yup, it got through this round lt;ggt;

gt; ... Not sure how to make it more general.

You might want to try the earlier suggestion posted
(looks to me a little simpler lt;ggt;, with an arb tiebreaker col to handle any
occurence of ties/multiple ties, irregardless of the number [not just zero])

Slightly adapted to suit your actual data set-up ..

Source data in A1:C6, expected max extent A1:C100 (say)
Results required within E1:G100

In E1: =IF(H1=quot;quot;,quot;quot;,ROW(A1))

In F1:

=IF(ISERROR(LARGE($H:$H,ROW(A1))),quot;quot;,INDEX(B:B,MAT CH(LARGE($H:$H,ROW(A1)),$H
:$H,0)))

F1 copied to G1

In H1: =IF(C1=quot;quot;,quot;quot;,C1-ROW()/10^10)

E1:H1 selected and filled down to H100

The above will return the required results in cols E to G, all neatly
bunched at the top, viz for the sample data posted, we'd get:

gt; Final Solution (E1:G6):
gt; 1 Figs 3
gt; 2 Apples 2
gt; 3 Carrots 1
gt; 4 Bananas 0
gt; 5 Durian 0
gt; 6 Eggs 0

Col E is a simple row counter to auto-number the rows
Col H is an arb tie-breaker col - can be hidden away
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Smurfettequot; gt; wrote in message
...
gt; Hi Max,
gt;
gt; See my solution below (hopefully this time it doesn't get lost!)
gt;
gt; Raw data (A1:C6):
gt; 1 Apples 2
gt; 2 Bananas 0
gt; 3 Carrots 1
gt; 4 Durian 0
gt; 5 Eggs 0
gt; 6 Figs 3
gt;
gt; Middle step (D16):
gt; 0
gt; 2
gt; 0
gt; 4
gt; 5
gt; 0
gt;
gt; Column D equation is: =IF(C1=0,$A1,0)
gt;
gt; Final Solution (E1:G6):
gt; 1 Figs 3
gt; 2 Apples 2
gt; 3 Carrots 1
gt; 4 Bananas 0
gt; 5 Durian 0
gt; 6 Eggs 0
gt;
gt; whe
gt; Column E numbers the rows of data
gt; Column F equation is:
gt;
=IF(G1=0,INDEX($B$1:$D$6,LARGE(D$1$6,(ROWS($A$1: $A$5) 1-$E1)),1),INDEX($B$
1:$D$6,MATCH(G1,C$1:C$6,0),1))
gt; Column G equation is: =LARGE($C$1:$C$6,$E1)
gt;
gt; Hope this makes sense. It works because the only number that gets
repeated
gt; in my case is '0'. Not sure how to make it more general. I've absolute
gt; referenced cells so I can easily copy and paste.
gt;
gt; Cheers,
gt; Debbie
Here is a slight variation on Max's play.
Assume this new data, names and position:

A B C J K M
Seq ListA ListB SortA SortB ListD
1 A 2 F 3 6
2 B 0 A 2 1
3 C 1 D 2 4
4 D 2 C 1 3
5 E 0 B 0 2
6 F 3 E 0 5

SortB contains Smurfette's original formula:
=LARGE(ListB,Seq)
The first and second (to be copied down) formulas of ListD a
=MATCH(K2,ListB,0)
=IF(K3=K2,MATCH(K3,INDEX(ListB,M2 1):INDEX(ListB,C OUNTA(ListB)),0) M2,
MATCH(K3,ListB,0))
SortA contains:
=INDEX(ListA,ListD)