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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|