2nd and last occurance

[fred]

https://docs.google.com/open?id=0B1Yt3Yl95LuPcE5EM2tGcHQ5VEU


Hi all,


I'm trying to write a formula to find out the 2nd and last occurence of accounts. See link. I can get the 2nd occurance but how do I identify the last occurance without using filter and manually work them out? My original sheet is thousands of lines. thanks.


Frederick

 

[maku]

I think this will work...if the data is already sorted by account name as in your example.

=IF(C2=2,"2nd Deal in account",IF(C3>C2,"","Last Deal"))

 

[maku]

If your data is not sorted...this will find the last deal in unsorted data.

=IF(IF(COUNTIF($A$2:$A$39,A2)=1,TRUE,COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$39,A2))=TRUE,"Last Deal","")

 

[Debraj]

Hi Fred,


In D2 Apply Array Formula as..

=IF(B2=MAX(IF($A$1:$A$39=A2,$B$1:$B$39,"")),"Last Deal",IF(B2=SMALL(IF($A$1:$A$39=A2,$B$1:$B$39,""),2),"2nd Deal",""))

Confirm the formula by pressing Ctrl Shift Enter, Not just Enter.

And drag below..

Hope it will works ..


Regards,

Deb

 

[NARAYANK991]

Hi ,


One more formula :


=IF(A3<>A2,IF(COUNTIF(OFFSET(A2,,,-(MATCH(A2,$A$1:A2,0))),A2)>1,"Last Deal in Account",""),IF(COUNTIF(OFFSET(A2,,,-(MATCH(A2,$A$1:A2,0))),A2)=2,"2nd Deal in Account",""))


This uses the following logic :


1. If the next value is different from the current value ( e.g. A3 <> A2 ) , if the number of entries is more than 1 , then display "Last Deal in Account" else display a blank.


2. If the next value is the same as the current value , if the count of the earlier values equals 2 , then display "2nd Deal in Account" else display a blank.


Narayan

 

[Faseeh]

Hi all,


This is nearly similar to Deb's formula, just one function is different:

=IF(LARGE(IF($A$2:$A$39=A3,$B$2:$B$39),1)=B3,"Last",IF(SMALL(IF($A$2:$A$39=A3,$B$2:$B$39),2)=B3,"2ND",""))

Ctrl+Shift+Enter


Regards,

 

[fred]

Thanks, all!!