Formula to check latest status of repeated reference no

[sanju987kumar]

I want to calculate the latest status of a repeated no....


No Status

123 Hold

234 Hold

123 Complete

345 Complete

234 Clarification

 

[NARAYANK991]

Hi Kumar ,


Use the following array formula , entered using CTRL SHIFT ENTER :


=INDEX(Status,MAX((Repeated_Nos=123)*(ROW(Repeated_Nos)-MIN(ROW(Repeated_Nos))+1)))


Repeated_Nos is the range of numbers , and Status is the range of status text strings.


123 is the number whose status you want ; if you want the status of a different number , change this 123 to that number.


Narayan

 

[Dhamo]

Hi Sanju,


Assuming Column 'A' has nos and column 'B' has Status. Row 1 is the header.

Please follow the steps.


1. Sort by 'No'

2. In C2 write this formula [=IF(A2<>A3,B2,)]

3. In Column 'C' Filter non blank values - Copy the area and paste it where you want.