help required on sorting

rajandvinu

New Member
Joined
Sep 7, 2006
Messages
12
Hello,

Please me on this one.

apple 10
grape 2
vine 3
sour 14
apple 2
vine 5
vine 6
apple 8

considering the above matrix i want to display the last apple's count in a matrix. How do we go about? please help.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
apologies for my wage statement above, what result i would want is :

Last apple count = 8
Last Grape count = 2
Last vine count = 6
Last sour count = 14
 
Upvote 0
Can you clarify please.
Do you mean you want to sort the list by column one and then column two or do you want a function that picks up the last instance of apple in the list and displays the value associated with it?
 
Upvote 0
Here's one way:
Book1
CDEFG
4tom1tom17
5****2
6harry3
7jane4
8polly5
9kelley6
10scott7
11brian8
12tom9
13****10
14harry11
15jane12
16polly13
17kelley14
18scott15
19brian16
20tom17
21****18
22harry19
23jane20
24polly21
25kelley22
26scott23
27brian24
Sheet3


G4: =INDEX($D$4:$D$27,MAX(IF($C$4:$C$27=F4,ROW($C$4:$C$27)-ROW($C$3),0))) confirmed with Ctrl+Shift+Enter... though the formula could be simplified to =INDEX(D:D,MAX(IF($C$4:$C$27=F4,ROW($C$4:$C$27),0))), also a CSE formula.
 
Upvote 0
Thank you very much it has worked. However i have another additional requirement to this formula. Rather than searching for the full word "TOM" - can we have a formula which checks every string.

For Eg : instead of "Changed category to Global 1st Line GSD" searching as a full word - can i have the parameter "Changed category to" but should still return last value irrespective of what is after that in the search matrix.

Just to simplify further :

Changed category to Global 1st Line GSD - resolved tickets  1
Changed read status to: Not read by owner  2
Request escalated to alert level 2  3
Changed read status to: Read by owner  4
Changed read status to: New information  5
Request added to ticket from internal web   6
Changed category to Global 1st Line GSD - resolved tickets  7
Changed read status to: Not read by owner  8
Request escalated to alert level 2  9
Changed read status to: Read by owner  10
Changed read status to: New information  11
Request added to ticket from internal web   12
Changed category to Global 1st Line GSD - resolved tickets  13
Changed read status to: Not read by owner  14
Request escalated to alert level 2  15
Changed read status to: Read by owner  16
Changed read status to: New information  17
Request added to ticket from internal web   18
Changed category to Global 2nd Line GSD - resolved tickets  19
Changed read status to: Not read by owner  20
Request escalated to alert level 2  21
Changed read status to: Read by owner  22
Changed read status to: New information  23
Request added to ticket from internal web   24




Changes Category to = 19
 
Upvote 0
Try this:

Code:
=INDEX(D:D,MAX(IF(LEFT($C$4:$C$27,LEN(F4))=F4,ROW($C$4:$C$27),0)))
 
Upvote 0

Forum statistics

Threads
1,217,476
Messages
6,136,883
Members
450,029
Latest member
MissQuotation

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top