Try this array formula (commit with CTRL+SHIFT+ENTER):

=IF(B2="",A2,IF(INDEX($B$2:B2,MATCH(1,--($B$2:B2=B2),0)-1)0,
E2&" ","")&B2&" "&A2)


msnyc07 wrote:
Hi, thanks, tried it and got '0's across the board. I can try to explain in
more detail I was hoping that would not add confusion.


The data I received was in a format like this

Name |Parent | Fullname
Foreman | ""
Level 1 | Foreman
Level 2 | Foreman
Dockmaster |
Level 1 | Dockmaster
Offhsore | Level 1
Level 2 | Dockmaster

and I need to put FullName back together like so:

Foreman
Foreman Level 1
Foreman Level 2
Dockmaster
Dockmaster Level 1
Dockmaster Level 1 Offshore
Dockmaster Level 2

Thus I need to
Check if a Record has a Parent Record
Use *that* Parent Record as a 'Prefix' to Concatenate FullName with

My first simple formula of
If Parent"" Fullname = Concatenate LastRecordFullname, Name else Name

This only works on the first 'child' record of course. I did add a 'and
ParentLastRecordParent but that is as far as I can get. What I need is
some sort of recursive 'Or With Record Before That Parent' and so on until it
reaches the first non-matching Parent Record above it so I can pull that
FullName into the Concatenation.

Does that help?

"T. Valko" wrote:

This works based on your very limited sample data.

=INDEX(A2:A5,LOOKUP(2,1/(A2:A5LOOKUP("zzzzz",A2:A5)),ROW(A2:A5))-ROW(A2)+1)

No error handling.

--
Biff
Microsoft Excel MVP


"msnyc07" wrote in message
...
I have a formula that is based on comparing the last value in a column.
Basically of not null it performs an action.

However I need the ones after that to be able to set up and find the last
different value e.g.

A
B
B
B

My formula so far is if Value"" then do X
I even got it to say If Value"" AND Value not equal to prior record.
Which would work for the second B

But what I really need it to say (to get to A) is
If Value "" and Value IS Equal to Last Record step backwards until you
find the value of the first different record.

Is that possible?

.