help required on sorting

Hello,

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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

rajandvinu

New Member
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
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?

as above

hatman

Well-known Member
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
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
Request escalated to alert level 2  3
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
Request escalated to alert level 2  9
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
Request escalated to alert level 2  15
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
Request escalated to alert level 2  21
Changed read status to: New information  23
Request added to ticket from internal web   24

Changes Category to = 19

hatman

Well-known Member
Try this:

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

Replies
0
Views
109
Replies
3
Views
93
Replies
3
Views
68
Replies
5
Views
125
Replies
3
Views
763

1,172,052
Messages
5,878,913
Members
433,383
Latest member
swisshome

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.

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

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