×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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:

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

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
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)

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

In your example, I entered this expression in column EF. SORRY

F: =D1=LEFT(E1,LEN(E1)-4)

...and COPY n PASTE down with this result...

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
 


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

(OP)
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)

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

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

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
 

F: =COUNTA(A1:E1)
G: =INDEX($A1:$E1,$F1-1)
H: =INDEX($A1:$E1,$F1)
I: =G1=LEFT(H1,LEN(H1)-4)


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close