Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Shimmy613

Programmer
Feb 14, 2011
39
US
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:

Code:
[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?
 
Hi,

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

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
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)
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
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)​
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top