G
Guest
did you get the speadsheet I sent?
Biff said:
B
Biff
did you get the speadsheet I sent?
Yes, I'll take a look at it tonight.
Biff
Yes, I'll take a look at it tonight.
Biff
G
Guest
Thanks.. you can send it to (e-mail address removed) if it is before 6am tomorrow
morning or to the bme email after 6am.
Thanks
morning or to the bme email after 6am.
Thanks
G
Guest
Biff,
I'm using your formula:
=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($A$1:$A$190)),ROW(A1)))
My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?
thanks in advance,
greg
I'm using your formula:
=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($A$1:$A$190)),ROW(A1)))
My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?
thanks in advance,
greg
B
Biff
You can use dynamic ranges that automatically adjust as the size of your
data set changes.
See instructions here:
http://contextures.com/xlNames01.html#Dynamic
Then the formula would be something like this: (array entered)
=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
Biff
data set changes.
See instructions here:
http://contextures.com/xlNames01.html#Dynamic
Then the formula would be something like this: (array entered)
=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
Biff
G
Guest
biff -
you nailed it kid. got the result i was looking for in terms of being able
to have a variable length array. here's my next question:
lets assume my formula reads as follows:
=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
....and my data looks like this:
a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4
if my $z$1 value is "y1"...the formula returns 2 data sets:
y1 p1
y1 p3
the thing is, i only want the result delivered if the value in (rng3) is ">0"
can you help me?
thanks in advance.
g
you nailed it kid. got the result i was looking for in terms of being able
to have a variable length array. here's my next question:
lets assume my formula reads as follows:
=INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
....and my data looks like this:
a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4
if my $z$1 value is "y1"...the formula returns 2 data sets:
y1 p1
y1 p3
the thing is, i only want the result delivered if the value in (rng3) is ">0"
can you help me?
thanks in advance.
g
B
Biff
i only want the result delivered if the value in (rng3) is ">0"
Try this: (array entered)
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
Biff
Try this: (array entered)
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
Biff
G
Guest
test
Biff said:
G
Guest
biff,
thanks for your help...getting an error. here is what you recommended:
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
...here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1
$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$500>0),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))
its still bringing back values from range 2 if range 3 is >0. in the
formula, it looks like were mutliplying the rng2 valule by the corresponding
value in rng3, however the rng2 value is not a number. it is in most cases
text. not sure if that is the problem. maybe we can just check to see if
rng3 is >0? i tried this, but syntax wasn't right. any thoughts?
thanks in advance,
g
thanks for your help...getting an error. here is what you recommended:
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
...here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1
$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PODetail'!K$1:K$500>0),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))
its still bringing back values from range 2 if range 3 is >0. in the
formula, it looks like were mutliplying the rng2 valule by the corresponding
value in rng3, however the rng2 value is not a number. it is in most cases
text. not sure if that is the problem. maybe we can just check to see if
rng3 is >0? i tried this, but syntax wasn't right. any thoughts?
thanks in advance,
g
G
Guest
Morning Biff. Thanks for the reply. Getting and error. Here's what you
recommended:
=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
this is my exact formula (array entered):
=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$500>0),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B87)))
it looks like we're mutiplying rng3 x corresponding value in rng1. one of
the issues is rng1 is text. is there a way to just check to see if rng3 is
greater than "0". i tried, but now luck.
any thoughts?
g
recommended:
=INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
this is my exact formula (array entered):
=INDEX('PO Detail'!D$1
$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PODetail'!K$1:K$500>0),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO
Detail'!B87)))
it looks like we're mutiplying rng3 x corresponding value in rng1. one of
the issues is rng1 is text. is there a way to just check to see if rng3 is
greater than "0". i tried, but now luck.
any thoughts?
g
R
Roger Govier
Hi
Your problem lies in the last part of the formula.
The argument for the SMALL() function that Biff proposed was ROW(A1)
which would be 1, but would be stepped up to 2, 3 etc. as you copied
down.
In this posting you are using ROW('PO Detail'!B87) and in your previous
posting ROW('PO Detail'!B87)
Change to ROW(A1) and it should work.
(N.B. It doesn't need to have any sheet reference, as it is not being
used to refer to any particular cell, it is just a method of getting the
smallest, 2nd smallest etc.)
Your problem lies in the last part of the formula.
The argument for the SMALL() function that Biff proposed was ROW(A1)
which would be 1, but would be stepped up to 2, 3 etc. as you copied
down.
In this posting you are using ROW('PO Detail'!B87) and in your previous
posting ROW('PO Detail'!B87)
Change to ROW(A1) and it should work.
(N.B. It doesn't need to have any sheet reference, as it is not being
used to refer to any particular cell, it is just a method of getting the
smallest, 2nd smallest etc.)
G
Guest
thanks for the input roger. the b87 ref was due to the fact that i was
copying the formula from the 87th row in my list. however it did set it back
to a1 and i'm showing below the formula from the 1st row in my range.
however i am still getting results of items for which the value in G$1:G$500
corresponding to rng2 is >0.
=INDEX('PO Detail'!D$1$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!G$1:G$500>0),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW(A1)))
thanks in advance for any help.
g
copying the formula from the 87th row in my list. however it did set it back
to a1 and i'm showing below the formula from the 1st row in my range.
however i am still getting results of items for which the value in G$1:G$500
corresponding to rng2 is >0.
=INDEX('PO Detail'!D$1
$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PODetail'!G$1:G$500>0),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW(A1)))
thanks in advance for any help.
g
R
Roger Govier
Hi
Biff posted
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
^^^^^
You posted
=INDEX('PO Detail'!D$1$500,
SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*
('PO Detail'!G$1:G$500>0),
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW(A1)))
You therefore seem to have
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng1)))),ROW(A1)))
^^^^^
This should not affect the result as it is only a way of getting an
array of results corresponding with the size of your table.
It certainly doesn't affect results with the original sample of data you
posted, which works perfectly with Biff's formula using rng1 or rng2
I don't know what your full set of data contains. I presume $B$3 does
contain the value you are looking for.
It should work OK.
Did you use Ctrl+Shift+Enter to make it an array formula when you
amended?
You say you are getting an error, what error is it coming up with?
Biff posted
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng2)))),ROW(A1)))
^^^^^
You posted
=INDEX('PO Detail'!D$1
$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*
('PO Detail'!G$1:G$500>0),
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW(A1)))
You therefore seem to have
=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng3>0),ROW(INDIRECT("1:"&ROWS(Rng1)))),ROW(A1)))
^^^^^
This should not affect the result as it is only a way of getting an
array of results corresponding with the size of your table.
It certainly doesn't affect results with the original sample of data you
posted, which works perfectly with Biff's formula using rng1 or rng2
I don't know what your full set of data contains. I presume $B$3 does
contain the value you are looking for.
It should work OK.
Did you use Ctrl+Shift+Enter to make it an array formula when you
amended?
You say you are getting an error, what error is it coming up with?
B
Biff
in the formula, it looks like were mutliplying the rng2
No, it's not a problem. It's not actually multiplying numbers. It's
multiplying logical tests that return either TRUE or FALSE. For example:
('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$500>0)
This will return an array like this:
(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)
These logicals are multiplied together and result in an array of 1's or 0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500 that's derived
from this expression:
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))
When the value_if_true condition is met then the corresponding numbers from
that expression are then passed to the Small function.
If you're not using dynamic named ranges then I would "dummy down" this
portion:
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))
To:
ROW($1:$500)
You don't actually need the sheet name or the cell references but you'll see
where some people use them just for a better understanding.
Biff
No, it's not a problem. It's not actually multiplying numbers. It's
multiplying logical tests that return either TRUE or FALSE. For example:
('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$500>0)
This will return an array like this:
(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)
These logicals are multiplied together and result in an array of 1's or 0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500 that's derived
from this expression:
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))
When the value_if_true condition is met then the corresponding numbers from
that expression are then passed to the Small function.
..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1
Detail'!K$1:K$500>0),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))
If you're not using dynamic named ranges then I would "dummy down" this
portion:
ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))
To:
ROW($1:$500)
You don't actually need the sheet name or the cell references but you'll see
where some people use them just for a better understanding.
Biff
G
Guest
Looked at some of you responses regarding excel and you seem very
knowledgeable. Have a question involving a lookup.
Scenario
Two Sheets
Sheet one
Has an invoice number on it.
Sheet two
Has a check number that may or may not have paid multiple invoices.
It is listed this way.
Column 1 column 2 column 3
et all
Check number invoice number invoice number
What I would like to do is use a lookup on sheet one that checks sheet two
for the invoice number (separate invoice numbers are located in columns B
through whatever) and returns what check it was paid on (checks located in
the left most column).
Can’t figure out an easy way to do it and was wondering if you had any ideas.
Regards,
jb
knowledgeable. Have a question involving a lookup.
Scenario
Two Sheets
Sheet one
Has an invoice number on it.
Sheet two
Has a check number that may or may not have paid multiple invoices.
It is listed this way.
Column 1 column 2 column 3
et all
Check number invoice number invoice number
What I would like to do is use a lookup on sheet one that checks sheet two
for the invoice number (separate invoice numbers are located in columns B
through whatever) and returns what check it was paid on (checks located in
the left most column).
Can’t figure out an easy way to do it and was wondering if you had any ideas.
Regards,
jb
M
Melanie
I'm also trying to do this. I need to lookup a value in one column that
returns multiple values in the second column, but I want to list it out with
"^" between the values. I need to do this dynamically for multiple lookup
values.
Can you help me?
returns multiple values in the second column, but I want to list it out with
"^" between the values. I need to do this dynamically for multiple lookup
values.
Can you help me?
T
T. Valko
You need to be more specific and provide some details.
M
Melanie
I have reoccuring group names in column A and multiple names (i.e tvalko,
debih, biff, etc.) in column B for each group. There can be one name or
unlimited names for a group. I want to identify the name s for each different
group and obtain the list on one line (tvalko^debih^biff) with carats as
delimeters.
debih, biff, etc.) in column B for each group. There can be one name or
unlimited names for a group. I want to identify the name s for each different
group and obtain the list on one line (tvalko^debih^biff) with carats as
delimeters.
T
T. Valko
There can be...unlimited names for a group.
Well, that's not good! You have to narrow down "unlimited".
There is a free add-in available that has a function that will do this but
the resulting string is *limited* to no more than 255 characters. So, that
means "tvalko^debih^biff" can't be more than 255 chars.
Are you interested in this?
Well, that's not good! You have to narrow down "unlimited".
There is a free add-in available that has a function that will do this but
the resulting string is *limited* to no more than 255 characters. So, that
means "tvalko^debih^biff" can't be more than 255 chars.
Are you interested in this?
M
Melanie
Yes, I'm definitely interested. I know in reality that it can't be unlimited,
but I need at least 10 names appended. I'll also have to figure out how to
move anything over 30 characters to the next line, but first things first.
Can this function handle multiple reoccurring group names (i.e. loop within
a loop)?
group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt
output:
mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt
The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.
but I need at least 10 names appended. I'll also have to figure out how to
move anything over 30 characters to the next line, but first things first.
Can this function handle multiple reoccurring group names (i.e. loop within
a loop)?
group name
marketing mjagger
marketing rthomas
marketing xbono
accounting rcharles
accounting jbrown
hr jmayer
it jjohnson
it bdylan
it jjoplin
it akiedis
it braitt
output:
mjagger^rthomas^xbono
rcharles^jbrown
jmayer
jjohnson^bdylan^jjoplin^akiedis^braitt
The looping seems to be the limitation I'm running into with the index
function or I'm using it incorrectly.