i need help

  • Thread starter Thread starter jad
  • Start date Start date
J

jad

Hi all,
i have the following information:

a1 b1
D:john
sam D:john
don D:john
lean D:John
D:michael
alex D:michael
david D:michael
D:robert
van D:robert
sam D:robert

so what's the forumla that i should enter on b2 to get this result
 
Hi all,
i have the following information:

a1 b1
D:john
sam D:john
don D:john
lean D:John
D:michael
alex D:michael
david D:michael
D:robert
van D:robert
sam D:robert

so what's the forumla that i should enter on b2 to get this result?
Click to expand...

Not in B2, but in B1 **array-enter** this formula:


=IF(LEFT(A1,2)="D:","",INDEX($A$1:A1,MAX(("D:"=LEFT($A$1:A1,2))*ROW($A$1:A1))))

To array enter a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula. Then copy/drag the formula down as
far as necessary.


--ron
 
Yep. I left the original data in column B for my testing (so I could match it
up).

I should have changed it to refer to column B.

In B2:
=IF(LEFT(A2,2)="D:","",IF(b1="",A1,b1))

Oops. Thanks for the correction.
 
Back
Top