Formula to find last different value in a column?
 

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?

Formula to find last different value in a column?
 

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?

Formula to find last different value in a column?
 

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?


.

Formula to find last different value in a column?
 

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?


.

Formula to find last different value in a column?
 

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?

.

Formula to find last different value in a column?
 

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?

.