The matching lines

[bines53]

Hello friends !

How do I insert the function ROW Into
=AGGREGATE(14,6,($C$2:$C$10)/($B$2:$B$10=$F$2),1) ,
To see the correct line number

Thank you !

 

[Somendra Misra]

Hi David,

Try in F6 and copy down:

=AGGREGATE(15,6,(ROW($C$2:$C$10)-ROW($C$2)+1)/(($C$2:$C$10=E6)*($B$2:$B$10=$F$2)),COUNTIF(E$6:E6,E6))

Regards,

 

[NARAYANK991]

Hi David ,

Try this :

=RIGHT(AGGREGATE(14,6,(($C$2:$C$10)+ROW($C$2:$C$10)%%)/($B$2:$B$10=$F$2),1),4)-1

where the highlighted 1 can be replaced by 2 and 3.

Narayan

 

[Somendra Misra]

Hi David,

As per suggestion of @NARAYANK991 Sir, I applied this formula, slightly bigger but works with duplicates also for same criteria.

=AGGREGATE(14,6,((ROW($C$2:$C$10)-ROW($C$2)+1)*($B$2:$B$10=$F$2))/((LARGE(($B$2:$B$10=$F$2)*($C$2:$C$10),ROWS(E$6:E6))=$C$2:$C$10)*($B$2:$B$10=$F$2)),MAX(1,MIN(ROWS(E$6:E6),SUMPRODUCT(--ISNUMBER(((ROW($C$2:$C$10)-ROW($C$2)+1)*($B$2:$B$10=$F$2))/((LARGE(($B$2:$B$10=$F$2)*($C$2:$C$10),ROWS(E$6:E6))=$C$2:$C$10)*($B$2:$B$10=$F$2)))))))

Regards,

 

[bines53]

Hi Narayan ,somendra ,

Excellent work !
@Narayan ,Huge trick !!!

Thank you !

David

 

[bines53]

Hi Narayan ,

I think the trick you gave, it seems that you can develop it, where to find reading material ?
I attach another file with the other functions, can also apply the trick here?


Thank you !

David