# help required on sorting

#### rajandvinu

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### 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
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?

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
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
Try this:

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

Replies
3
Views
763
Replies
9
Views
225
Replies
0
Views
378
Replies
5
Views
234
Replies
17
Views
333

Threads
1,172,043
Messages
5,878,871
Members
433,380
Latest member
Hadfield

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

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