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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

rajandvinu

New Member
Joined
Sep 7, 2006
Messages
12
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
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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?
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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.
 

rajandvinu

New Member
Joined
Sep 7, 2006
Messages
12
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
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

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
Top