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?

This works based on your very limited sample data.

=INDEX(A2:A5,LOOKUP(2,1/(A2:A5<LOOKUP("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?

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
Parent<LastRecordParent 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:A5<LOOKUP("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?


.

Wow!

After studying the desired result I have no idea how you'd do that. I don't
think you'll be able to do that using formulas.

--
Biff
Microsoft Excel MVP


"msnyc07" wrote in message
...
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
Parent<LastRecordParent 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:A5<LOOKUP("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?


.

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
Parent<LastRecordParent 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:A5<LOOKUP("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?

.

Ignore that last one...this should be complete (still an array formula):

=IF(B2="",A2,IF(INDEX($B$2:B2,MATCH(1,--($B$2:B2=B2),0)-1)<0,
INDEX($B$2:B2,MATCH(1,--($B$2:B2=B2),0)-1)&" ","")&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
Parent<LastRecordParent 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:A5<LOOKUP("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?

.