Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smurfette
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column in

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



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column in

One play using non-array formulas ..

Put in K1:
=IF(ISERROR(LARGE($M:$M,ROW(A1))),"",INDEX(A:A,MAT CH(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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Smurfette" wrote in message
...
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!!





  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smurfette
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column

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" wrote:

One play using non-array formulas ..

Put in K1:
=IF(ISERROR(LARGE($M:$M,ROW(A1))),"",INDEX(A:A,MAT CH(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)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Smurfette" wrote in message
...
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!!






  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column

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
http://savefile.com/projects/236895
xdemechanik
---
"Smurfette" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Smurfette
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column

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 (D1:D6):
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:D$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" wrote:

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
http://savefile.com/projects/236895
xdemechanik
---
"Smurfette" wrote in message
...
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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column

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

Thanks for posting back. Yup, it got through this round <g

... 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,MAT CH(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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Smurfette" wrote in message
...
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 (D1:D6):
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:D$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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default Index? Match? Function to sort and return value fr diff column

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)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
How do I pull a date from a separate worksheet using the IF functi Jerrod Mason Excel Worksheet Functions 8 July 30th 05 06:35 AM
Match and Index jdeumer Excel Worksheet Functions 0 July 14th 05 10:23 AM
HELP! How do you--> Lock a set of rows but also link worksheets to FRUSTRATED Excel Discussion (Misc queries) 6 December 29th 04 11:05 PM
index to a range of cells Frank Kabel Excel Worksheet Functions 0 October 27th 04 05:39 PM


All times are GMT +1. The time now is 06:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"