G
Guest
hi, am wondering how to find the last copy of a text in a column, e.g.:
"fred"
any ideas? thanks
"fred"
any ideas? thanks
T
T. Valko
Do you mean there are multiple instances of "Fred" and you want to find the
last instance?
What exactly do you want to do when you find the last instance?
...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50
This formula finds the last instance of "Fred" and returns the corresponding
value from column B:
=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)
last instance?
What exactly do you want to do when you find the last instance?
...........A..........B
1.....Fred.......10
2.....Biff.........22
3.....Fred.......17
4.....Fred.......11
5.....Lisa........50
This formula finds the last instance of "Fred" and returns the corresponding
value from column B:
=LOOKUP(2,1/(A1:A5="Fred"),B1:B5)
G
Guest
hi, thanks for the reponse, saw previous similar question using word fred,
got got a response,
this was my nth posting.. last occurance of fred is
the idea.
other info is:
hi, trying to find way to locate text "up" from current position. not sure
which function / how to use. have been looking at: MATCH, LOOKUP.. thanks
example have for find last number in a column is:
=P54=LOOKUP(9.99999999999999E+307,P$24
$59)
-----
example using for finding next instance "down" from current position is:
MATCH(TRUE,OFFSET($AT427:$AT$1231,1,0)="dn",0)
array portion: guessing would be able to search from fixed top position of
document, down to current position. e.g.: $AT$128:$AT427,-1
***
will be using range before "current" cell/ position to find the letters: "dn"
got got a response,
this was my nth posting.. last occurance of fred isthe idea.
other info is:
hi, trying to find way to locate text "up" from current position. not sure
which function / how to use. have been looking at: MATCH, LOOKUP.. thanks
example have for find last number in a column is:
=P54=LOOKUP(9.99999999999999E+307,P$24
$59)-----
example using for finding next instance "down" from current position is:
MATCH(TRUE,OFFSET($AT427:$AT$1231,1,0)="dn",0)
array portion: guessing would be able to search from fixed top position of
document, down to current position. e.g.: $AT$128:$AT427,-1
***
will be using range before "current" cell/ position to find the letters: "dn"
G
Guest
just looking for fred, not different column data, is that what you have?
G
Guest
looking for cell reference to "last" occurance of text, in 1 column. e.g.:
"fred"
"fred"
T
T. Valko
Ok...
A1...Fred
A2...Biff
A3...Fred
A4...Fred
A5...Lisa
="A"&LOOKUP(2,1/(A1:A5="Fred"),ROW(A1:A5))
Returns: A4
A1...Fred
A2...Biff
A3...Fred
A4...Fred
A5...Lisa
="A"&LOOKUP(2,1/(A1:A5="Fred"),ROW(A1:A5))
Returns: A4
G
Guest
hi, thanks.. I got that to work, trying to incorporate that into a hyperlink
formula that is very useful for moving down a document; I was trying to
reverse the idea, to move up a document.
first example works for down, keys on: "dn" that col.
2nd example problem: looking for an UP formula that keys off same "dn", in
that same column. will put up formula in next column to right, same row.
thanks.
1st example, for moving down a document seems to work well, but do you think
it was properly done..? anyways, note: ctrl-shift-enter array / does not
cut paste well (at least if calculation is turned off). working down example
is:
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$1232,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$Z$3,1))),"dn")
NOT WORKING UP Example is:
problems might be too many row(offsets ?
note: using start of document rows / use of "$" signs, and -1's in offset
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),-1,1))),"u")
XXXXXXXXX
formula that is very useful for moving down a document; I was trying to
reverse the idea, to move up a document.
first example works for down, keys on: "dn" that col.
2nd example problem: looking for an UP formula that keys off same "dn", in
that same column. will put up formula in next column to right, same row.
thanks.
1st example, for moving down a document seems to work well, but do you think
it was properly done..? anyways, note: ctrl-shift-enter array / does not
cut paste well (at least if calculation is turned off). working down example
is:
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$1232,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$Z$3,1))),"dn")
NOT WORKING UP Example is:
problems might be too many row(offsets ?
note: using start of document rows / use of "$" signs, and -1's in offset
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))),-1,1))),"u")
XXXXXXXXX
G
Guest
$Z$3 is the amount of rows down, for the offset.
G
Guest
hi, example working with, that seems to be correct for what trying to do, is:
=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))
placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")
trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")
=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))
placed in row 141, that has a "dn", is not included,
and result: 29 is correct (prev row with "dn")
trying to place in formula, does not seem to take / allow to enter:
purpose of formula is for shortcut up to next text occurance of "dn".
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")
T
T. Valko
Sorry, but I have no idea what you're trying to do with this.
G
Guest
have 2 hyperlink formulas, making for use "in-document" that allows a
shortcut jump "down" to the next copy of the same hyperlink (keys off the
friendly name: "dn"). where the down hyperlink is working, I was trying to
develope a hyperlink that jumps up to the last "dn" - above the current line
that also holds the down link. the right link is placed in the column to the
right, or column AU, any column same row, doesn't matter. I don't have the
"up" link working yet. the base formula (last item below) is working.
problem is in setup of hyperlink.. thanks.
have an extensive example of a hyperlink, but should be easy to see
portions, with line returns to help separate sections.. thanks in advance
version that works for "dn" shortcut, (to next copy of this link) is in fact
what the 2nd (non-workin) version is keying off.
note: cntrl-shift-enter array, $z$3 is number of rows to move down (size
of screen), placed in row 141
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$1232,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$Z$3,1))),"dn")
Modified version that is not quite right, can not enter / am probably using
functions / variables not needed ??? thanks, non-working example is:
note: placed in row 141, column to right, AU
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")
base formula working with, that seems to be working: row 141 is not
included in query, previous / last occurance of "dn" is correctly identified.
=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))
shortcut jump "down" to the next copy of the same hyperlink (keys off the
friendly name: "dn"). where the down hyperlink is working, I was trying to
develope a hyperlink that jumps up to the last "dn" - above the current line
that also holds the down link. the right link is placed in the column to the
right, or column AU, any column same row, doesn't matter. I don't have the
"up" link working yet. the base formula (last item below) is working.
problem is in setup of hyperlink.. thanks.
have an extensive example of a hyperlink, but should be easy to see
portions, with line returns to help separate sections.. thanks in advance
version that works for "dn" shortcut, (to next copy of this link) is in fact
what the 2nd (non-workin) version is keying off.
note: cntrl-shift-enter array, $z$3 is number of rows to move down (size
of screen), placed in row 141
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT141:$AT$1232,1,0))-ROW(OFFSET($AT141,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($AT141:$AT$1232,1,0)),
MATCH(TRUE,OFFSET($AT141:$AT$1232,1,0)="dn",0))),$Z$3,1))),"dn")
Modified version that is not quite right, can not enter / am probably using
functions / variables not needed ??? thanks, non-working example is:
note: placed in row 141, column to right, AU
=HYPERLINK(IF(ISNA(INDEX(ROW(OFFSET($AT$129:$AT141,-1,0))-ROW(OFFSET($AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),"",
"#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$A141),"$",""),ROW(),"")&INDEX(ROW(OFFSET($A$129:$AT141,-1,0)),
LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0))))),-1,1))),"u")
base formula working with, that seems to be working: row 141 is not
included in query, previous / last occurance of "dn" is correctly identified.
=LOOKUP(2,1/(OFFSET($AT$129:$AT141,-1,0)="dn"),ROW(OFFSET($AT$129:$AT141,-1,0)))