Find last occurrence of each unique value in column

[dazed007]

Hello, hoping this is straight forward but having spent quite a while searching I'm struggling to find exactly what I'm looking for.

Please could someone provide a formula to go into column B to show the last occurrence for each unique value in column A.

For example:

A	B
BN1
BN2
BN1
BN4	Yes
BN3
BN3	Yes
BN2	Yes
BN1
BN1	Yes

<tbody>
</tbody>

Thanks.

 

[XOR LX]

Hi.

Assuming your list is in A1:A9, this array formula** in B1:

=REPT("Yes",ROWS($1:1)=MATCH(1,0/(A$1:A$9=A1)))

Copy down as required.

Note that you can amend the upper range reference (A9 here) to an appropriately larger value as required.

However, since this is an array formula, you should know that you cannot make it arbitrarily large without any detriment to performance: each cell referenced, whether beyond your last-used cell in that range or not, will be calculated. Using A:A here, for example, would not be a very good idea!

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[dazed007]

Working!

Thanks and understood, it seems to be behaving OK, the range is in a table which looks to be working dynamically so all good cheers.

 

[XOR LX]

Ah, yes, I forgot about data tables. Even better, then!

Cheers