Index? Match? Function to sort and return value fr diff column in

[Guest]

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

 

[Max]

One play using non-array formulas ..

Put in K1:
=IF(ISERROR(LARGE($M:$M,ROW(A1))),"",INDEX(A:A,MATCH(LARGE($M:$M,ROW(A1)),$M
:$M,0)))
Copy K1 to L1

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

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

Cols K & L will auto-return a full descending sort of cols A & 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 & B.

For the sample data posted, we'd get:

F 3
A 2
C 1
B 0
D 0
E 0
("blank" rows below)

 

[Guest]

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

 

[Max]

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?

 

[Guest]

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

where:
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

 

[Max]

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

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

You might want to try the earlier suggestion posted
(looks to me a little simpler <g>, 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="","",ROW(A1))

In F1:

=IF(ISERROR(LARGE($H:$H,ROW(A1))),"",INDEX(B:B,MATCH(LARGE($H:$H,ROW(A1)),$H
:$H,0)))

F1 copied to G1

In H1: =IF(C1="","",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:

Final Solution (E1:G6):
1 Figs 3
2 Apples 2
3 Carrots 1
4 Bananas 0
5 Durian 0
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

---

 

[Herbert Seidenberg]

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 are:
=MATCH(K2,ListB,0)
=IF(K3=K2,MATCH(K3,INDEX(ListB,M2+1):INDEX(ListB,COUNTA(ListB)),0)+M2,
MATCH(K3,ListB,0))
SortA contains:
=INDEX(ListA,ListD)