A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula to find last different value in a column?



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2010, 06:02 AM posted to microsoft.public.excel.worksheet.functions
msnyc07
external usenet poster
 
Posts: 97
Default 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?
  #2  
Old February 11th, 2010, 06:41 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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: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?



  #3  
Old February 11th, 2010, 03:17 PM posted to microsoft.public.excel.worksheet.functions
msnyc07
external usenet poster
 
Posts: 97
Default 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
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?



.

  #4  
Old February 11th, 2010, 07:16 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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
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?



.



  #5  
Old February 11th, 2010, 10:29 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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
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?


.

  #6  
Old February 11th, 2010, 10:33 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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
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?


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.