=SMALL( IF( MATCH(A1:A7, A1:A7, 0) = ROW(A1:A7) - MIN(ROW(A1:A7) ) + 1, A1:A7, ""), 2) again array entered change red 2 for 3rd value etc.
credit to shg an nbvc at excel forum for these
ADVERTISEMENT
Hi,
a possibility (same formula from Martindwilson)
=IF(COUNTBLANK(A1:A7)>0,SMALL(IF(MATCH(A1:A7+0,A1:A7+0,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2+1),SMALL(IF(MATCH(A1:A7,A1:A7,0)=ROW(A1:A7)-MIN(ROW(A1:A7) )+1,A1:A7,""),2))
Hope it helps
Edit: sorry I've forgotten a ";" in the formula from my Italian settings
25 | 26 |
26 | |
32 | |
27 | |
31 | |
25 |