help required on sorting

[rajandvinu]

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.

 

[rajandvinu]

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]

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?

 

[rajandvinu]

as above

 

[hatman]

Here's one way:

Book1
	C	D	E	F	G
4	tom	1		tom	17
5	****	2
6	harry	3
7	jane	4
8	polly	5
9	kelley	6
10	scott	7
11	brian	8
12	tom	9
13	****	10
14	harry	11
15	jane	12
16	polly	13
17	kelley	14
18	scott	15
19	brian	16
20	tom	17
21	****	18
22	harry	19
23	jane	20
24	polly	21
25	kelley	22
26	scott	23
27	brian	24
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]

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]

Try this:

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