Last Instance of a record in a list

[Mike952]

Hi Folks,

I want the word "Yes" to appear in column C, adjacent to the last record in a list of duplicate entries in a column.

(see attached).

e.g. the record 1405800475 (in Column A) should have "Yes" in cell C86.

Each week I add about 1000 new rows to the bottom of the list, so I need to be able to drag it down to keep the last record updated.

Hope this is clear.

Any help would as always, be gratefully appreciated.

Mike

[TMS]

Try:

Please Login or Register  to view this content.

in row 2 and copied down

Regards, TMS

[TMS]

And maybe:

Please Login or Register  to view this content.

Regads, TMS

[Mike952]

HI TMS,

that works perfectly.

is there an advantage to the second formula?

MIke

[jason.b75]

I've found from previous threads that using COUNTIF or a similar function with an expandable range causes excel to hang after it's copied down a few thousand rows.

This should be more stable.

For first record, try this formula in B2

=IF(ISNUMBER(MATCH(A2,A1:A$1,0)),"No","Yes")

For last record,

Define a named range called 'lastcell'

In the refers to box, enter the formula, =INDEX(Sheet1!$A:$A,MATCH(1e+100,Sheet!$A:$A))

Then use this formula in C2

=IF(ISNUMBER(MATCH(A2,A3:lastcell,0)),"no","yes")

Then use autofill to copy down.

[TMS]

You're welcome. Thanks for the rep.

is there an advantage to the second formula?

No. It just differentiates between the last of many and the last where the last is the only one. Depends if that's useful or not

[TMS]

@jason.b75

... an expandable range causes excel to hang ...

Not sure what that means. I've never encountered that issue. You appear to have a similar construct to mine, given that MATCH(A2,A1:A$1,0) will expand; A$1 is static and A1 will increase as the formula is dragged down.

Your Named Formula has an error:

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

And the last outcome looks as though it may be wrong; at least, we don't agree on the outcome. Cell A1 has 1405817397 and I think that is the first, and only entry, and therefore the last entry for that value.

Regards, TMS

[jason.b75]

Hi Trevor

To elaborate on the expanding range issue, I've only noticed it with countif(s), etc. Match and other functions seem to be ok, maybe it's something caused by the range object requirement in the first argument?

Try copying the records in column A down for 15-20k rows, then compare calculation times.

For me, the countif method takes about 10 seconds to autofill, the match method takes about 1 second.

Subsequently, adding extra records in sets of 1k rows (the OP's approximate weekly addition), takes about 4 seconds to process for countif, with a noticeable increase in the strain as the overall volume of data increases.

For the discrepancy in the results, I forgot to include an adjustment in the named range formula, it should have been

=INDEX(Sheet1!$A:$A,MATCH(1e+100,Sheet1!$A:$A)+1)

Otherwise the last row will always be incorrectly matched to itself instead of looking for a subsequent (non-existent) record.