## Excel formula question: finding last two columns that are almost identical

## Excel formula question: finding last two columns that are almost identical

(OP)

I have a filepath in each row. I've broken up the path using the backslash as the delimiter. So the columns look like this:

What I'm trying to do is flag the rows that have the same values in the last two rows, ignoring the

In the example shown above, the 2nd and 3rd rows should be flagged.

How can this be done?

#### CODE

```
C My Data Bob's Stuff Monday green.lnk
C My Data Bob's Stuff Monday Monday.lnk
C My Data Bob's Stuff Tuesday Tuesday.lnk
C My Data Bob's Stuff Tuesday blue.lnk
```

What I'm trying to do is flag the rows that have the same values in the last two rows, ignoring the

**.lnk**extension. The number of filled cells in each row will vary, so only the last two-non blank cells in each row should be compared.In the example shown above, the 2nd and 3rd rows should be flagged.

How can this be done?

## RE: Excel formula question: finding last two columns that are almost identical

Are you an Excel user of some experience?

What have you tried?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel formula question: finding last two columns that are almost identical

O is the last row with data. So this seems to get me the last pertinent cell in each row:

=

LOOKUP(2,1/(A1:O1<>""),A1:O1)

But I can't reliably get the second to last cell. I tried this but it doesn't consistently work:

=INDEX(A1:O1,MATCH(

LOOKUP(2,1/(A1:O1<>""),A1:O1),A1:O1)-1)## RE: Excel formula question: finding last two columns that are almost identical

~~E~~F. SORRYF:

=D1=LEFT(E1,LEN(E1)-4)...and COPY n PASTE down with this result...

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel formula question: finding last two columns that are almost identical

=INDEX(A1:O1,MATCH(LOOKUP(2,1/(A1:O1<>""),A1:O1),A1:O1)-1)

## RE: Excel formula question: finding last two columns that are almost identical

"The number of filled cells in each row will vary"

F:

=COUNTA(A1:E1)G:

=INDEX($A1:$E1,$F1-1)H:

=INDEX($A1:$E1,$F1)I:

=G1=LEFT(H1,LEN(H1)-4)Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein