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

[Shimmy613]

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:

[tt]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[/tt]

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?

 

[SkipVought]

Hi,

Are you an Excel user of some experience?
What have you tried?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Shimmy613]

Right. Good point. Sorry about that.

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)

 

[SkipVought]

In your example, I entered this expression in column EF. SORRY
[tt]
F: =D1=LEFT(E1,LEN(E1)-4)
[/tt]
...and COPY n PASTE down with this result...

[pre]
C My Data Bob's Stuff Monday Green.lnk FALSE
C My Data Bob's Stuff Monday Monday.lnk TRUE
C My Data Bob's Stuff Tuesday Tuesday.lmk TRUE
C My Data Bob's Stuff Tuesday Blue.lnk FALSE
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Shimmy613]

Thank you, Skip, but like I wrote:

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)​

 

[SkipVought]

Oops. Just noticed your caveat...
"The number of filled cells in each row will vary"

[pre]
A B C D E F G H I
C My Data Bob's Stuff Monday Green.lnk 5 Monday Green.lnk FALSE
C My Data Monday Monday.lnk 4 Monday Monday.lnk TRUE
C My Data Bob's Stuff Tuesday Tuesday.lmk 5 Tuesday Tuesday.lmk TRUE
C My Data Bob's Stuff Tuesday Blue.lnk 5 Tuesday Blue.lnk FALSE
[/pre]
[tt]
F: =COUNTA(A1:E1)
G: =INDEX($A1:$E1,$F1-1)
H: =INDEX($A1:$E1,$F1)
I: =G1=LEFT(H1,LEN(H1)-4)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein